Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <pre><code>--All GRANT INSERT, UPDATE, SELECT ON Customers TO Joe, Mary --Revoke UPDATE access to table Customers for user Joe REVOKE UPDATE ON Customers to Joe --Cant Delete DENY DELETE ON Customers to Joe, Mary --Can Exec StoredProcedure GRANT EXEC ON uspInsertCustomers TO Joe --Grant Create Table GRANT CREATE TABLE TO Joe </code></pre> <p>This Displays User Info</p> <pre><code>CREATE procedure [dbo].[List_DBRoles] ( @database nvarchar(128)=null, @user varchar(20)=null, @dbo char(1)=null, @access char(1)=null, @security char(1)=null, @ddl char(1)=null, @datareader char(1)=null, @datawriter char(1)=null, @denyread char(1)=null, @denywrite char(1)=null ) as declare @dbname varchar(200) declare @mSql1 varchar(8000) CREATE TABLE #DBROLES ( DBName sysname not null, UserName sysname not null, db_owner varchar(3) not null, db_accessadmin varchar(3) not null, db_securityadmin varchar(3) not null, db_ddladmin varchar(3) not null, db_datareader varchar(3) not null, db_datawriter varchar(3) not null, db_denydatareader varchar(3) not null, db_denydatawriter varchar(3) not null, Cur_Date datetime not null default getdate() ) DECLARE DBName_Cursor CURSOR FOR select name from master.dbo.sysdatabases where name not in ('mssecurity','tempdb') Order by name OPEN DBName_Cursor FETCH NEXT FROM DBName_Cursor INTO @dbname WHILE @@FETCH_STATUS = 0 BEGIN Set @mSQL1 = ' Insert into #DBROLES ( DBName, UserName, db_owner, db_accessadmin, db_securityadmin, db_ddladmin, db_datareader, db_datawriter, db_denydatareader, db_denydatawriter ) SELECT '+''''+@dbName +''''+ ' as DBName ,UserName, '+char(13)+ ' Max(CASE RoleName WHEN ''db_owner'' THEN ''Yes'' ELSE ''No'' END) AS db_owner, Max(CASE RoleName WHEN ''db_accessadmin '' THEN ''Yes'' ELSE ''No'' END) AS db_accessadmin , Max(CASE RoleName WHEN ''db_securityadmin'' THEN ''Yes'' ELSE ''No'' END) AS db_securityadmin, Max(CASE RoleName WHEN ''db_ddladmin'' THEN ''Yes'' ELSE ''No'' END) AS db_ddladmin, Max(CASE RoleName WHEN ''db_datareader'' THEN ''Yes'' ELSE ''No'' END) AS db_datareader, Max(CASE RoleName WHEN ''db_datawriter'' THEN ''Yes'' ELSE ''No'' END) AS db_datawriter, Max(CASE RoleName WHEN ''db_denydatareader'' THEN ''Yes'' ELSE ''No'' END) AS db_denydatareader, Max(CASE RoleName WHEN ''db_denydatawriter'' THEN ''Yes'' ELSE ''No'' END) AS db_denydatawriter from ( select b.name as USERName, c.name as RoleName from ' + @dbName+'.dbo.sysmembers a '+char(13)+ ' join '+ @dbName+'.dbo.sysusers b '+char(13)+ ' on a.memberuid = b.uid join '+@dbName +'.dbo.sysusers c on a.groupuid = c.uid )s Group by USERName order by UserName' --Print @mSql1 Execute (@mSql1) FETCH NEXT FROM DBName_Cursor INTO @dbname END CLOSE DBName_Cursor DEALLOCATE DBName_Cursor Select * from #DBRoles where ((@database is null) OR (DBName LIKE '%'+@database+'%')) AND ((@user is null) OR (UserName LIKE '%'+@user+'%')) AND ((@dbo is null) OR (db_owner = 'Yes')) AND ((@access is null) OR (db_accessadmin = 'Yes')) AND ((@security is null) OR (db_securityadmin = 'Yes')) AND ((@ddl is null) OR (db_ddladmin = 'Yes')) AND ((@datareader is null) OR (db_datareader = 'Yes')) AND ((@datawriter is null) OR (db_datawriter = 'Yes')) AND ((@denyread is null) OR (db_denydatareader = 'Yes')) AND ((@denywrite is null) OR (db_denydatawriter = 'Yes')) </code></pre>
    singulars
    1. This table or related slice is empty.
    1. POHow to deny update permissions to one SQL table, but allow for others?
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USKeyboardFriendly
    1. USKeyboardFriendly
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COWhat is the difference between Update and update access?
      singulars
      1. PO
      1. USuser1542296
    2. CONothing That was a Typo, I updated it!
      singulars
      1. PO
      1. USKeyboardFriendly
    3. COAlso note that `DENY` trumps any `GRANT` while `REVOKE` just removes the `GRANT` (or `DENY`). So if you `DENY` a permission you will not be able to `GRANT` it back in any way short of making the user `DBO` or `sysadmin` or of course you can `REVOKE` the `DENY`.
      singulars
      1. PO
      1. USKenneth Fisher
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload