Search This Blog


SQL USERS Completed Reference - Part1

SQL Server query to find list of all users in a database
SQL Server query to find all permissions/access for all users in a database

SQL USERS Completed Reference -Part2

This Stored Procedure will provide a list of permissions that a user has either applied directly to the user account, or through roles under all databases

Eg: EXEC [master].dbo.uspGetPermissionsOfAllLogins_DBsOnColumns ;


USE [master] ;
GO
IF EXISTS
(
    SELECT * FROM sys.objects
    WHERE object_id = OBJECT_ID(N'dbo.uspGetPermissionsOfAllLogins_DBsOnColumns')
    AND [type] in (N'P',N'PC')
)
BEGIN
    DROP PROCEDURE dbo.uspGetPermissionsOfAllLogins_DBsOnColumns ;
END
GO
CREATE PROCEDURE dbo.uspGetPermissionsOfAllLogins_DBsOnColumns
AS
SET NOCOUNT ON
;
BEGIN TRY
    IF EXISTS
    (
        SELECT * FROM tempdb.dbo.sysobjects
        WHERE id = object_id(N'[tempdb].dbo.[#permission]')
    )
    DROP TABLE #permission
    ;
    IF EXISTS
    (
        SELECT * FROM tempdb.dbo.sysobjects
        WHERE id = object_id(N'[tempdb].dbo.[#userroles_kk]')
    )
    DROP TABLE #userroles_kk
    ;
    IF EXISTS
    (
        SELECT * FROM tempdb.dbo.sysobjects
        WHERE id = object_id(N'[tempdb].dbo.[#rolemember_kk]')
    )
    DROP TABLE #rolemember_kk
    ;
    IF EXISTS
    (
        SELECT * FROM tempdb.dbo.sysobjects
        WHERE id = object_id(N'[tempdb].dbo.[##db_name]')
    )
    DROP TABLE ##db_name
    ;
    DECLARE
    @db_name VARCHAR(255)
    ,@sql_text VARCHAR(MAX) 
    ;
    SET @sql_text =
    'CREATE TABLE ##db_name
    (
        LoginUserName VARCHAR(MAX)
        ,' 
    ;
    DECLARE cursDBs CURSOR FOR 
        SELECT [name]
        FROM sys.databases
        ORDER BY [name]
    ;
    OPEN cursDBs 
    ;
    FETCH NEXT FROM cursDBs INTO @db_name 
    WHILE @@FETCH_STATUS = 0 
        BEGIN 
                SET @sql_text =
        @sql_text + QUOTENAME(@db_name) + ' VARCHAR(MAX)
        ,' 
                FETCH NEXT FROM cursDBs INTO @db_name 
        END 
    CLOSE cursDBs 
    ;
    SET @sql_text =
        @sql_text + 'IsSysAdminLogin CHAR(1)
        ,IsEmptyRow CHAR(1)
    )' 

    --PRINT @sql_text
    EXEC (@sql_text)
    ;
    DEALLOCATE cursDBs 
    ;
    DECLARE
    @RoleName VARCHAR(255) 
    ,@UserName VARCHAR(255) 
    ;
    CREATE TABLE #permission 
    (
     LoginUserName VARCHAR(255)
     ,databasename VARCHAR(255)
     ,[role] VARCHAR(255)
    ) 
    ;
    DECLARE cursSysSrvPrinName CURSOR FOR 
        SELECT [name]
        FROM sys.server_principals 
        WHERE
        [type] IN ( 'S', 'U', 'G' )
        AND principal_id > 4
        AND [name] NOT LIKE '##%'
        ORDER BY [name]
    ;
    OPEN cursSysSrvPrinName
    ;
    FETCH NEXT FROM cursSysSrvPrinName INTO @UserName 
    WHILE @@FETCH_STATUS = 0 
    BEGIN 
        CREATE TABLE #userroles_kk 
        ( 
             databasename VARCHAR(255)
             ,[role] VARCHAR(255)
        ) 
        ;
        CREATE TABLE #rolemember_kk 
        ( 
             dbrole VARCHAR(255)
             ,membername VARCHAR(255)
             ,membersid VARBINARY(2048)
        ) 
        ;
        DECLARE cursDatabases CURSOR FAST_FORWARD LOCAL FOR
        SELECT [name]
        FROM sys.databases
        ORDER BY [name]
        ;
        OPEN cursDatabases
        ;
        DECLARE 
        @DBN VARCHAR(255)
        ,@sqlText NVARCHAR(4000)
        ;
        FETCH NEXT FROM cursDatabases INTO @DBN
        WHILE @@FETCH_STATUS = 0
        BEGIN
            SET @sqlText =
    N'USE ' + QUOTENAME(@DBN) + ';
    TRUNCATE TABLE #RoleMember_kk 
    INSERT INTO #RoleMember_kk 
    EXEC sp_helprolemember 
    INSERT INTO #UserRoles_kk
    (DatabaseName,[Role])
    SELECT db_name(),dbRole
    FROM #RoleMember_kk
    WHERE MemberName = ''' + @UserName + '''
    '

            --PRINT @sqlText ;
            EXEC sp_executesql @sqlText ;
        FETCH NEXT FROM cursDatabases INTO @DBN
        END
        CLOSE cursDatabases
        ;
        DEALLOCATE cursDatabases
        ;
        INSERT INTO #permission 
        SELECT
        @UserName 'user'
        ,b.name
        ,u.[role]
        FROM
        sys.sysdatabases b
        LEFT JOIN
        #userroles_kk u 
            ON QUOTENAME(u.databasename) = QUOTENAME(b.name)
        ORDER  BY 1 
        ;
        DROP TABLE #userroles_kk
        ; 
        DROP TABLE #rolemember_kk
        ;
        FETCH NEXT FROM cursSysSrvPrinName INTO @UserName 
    END 
    CLOSE cursSysSrvPrinName 
    ;
    DEALLOCATE cursSysSrvPrinName 
    ;
    TRUNCATE TABLE ##db_name 
    ;
    DECLARE
    @d1 VARCHAR(MAX)
    ,@d2 VARCHAR(MAX)
    ,@d3 VARCHAR(MAX)
    ,@ss VARCHAR(MAX)
    ;
    DECLARE cursPermisTable CURSOR FOR
        SELECT * FROM #permission 
        ORDER BY 2 DESC 
    ;
    OPEN cursPermisTable
    ;
    FETCH NEXT FROM cursPermisTable INTO @d1,@d2,@d3
    WHILE @@FETCH_STATUS = 0 
    BEGIN 
        IF NOT EXISTS
        (
            SELECT 1 FROM ##db_name WHERE LoginUserName = @d1
        )
        BEGIN 
            SET @ss =
            'INSERT INTO ##db_name(LoginUserName) VALUES (''' + @d1 + ''')' 
            EXEC (@ss) 
            ;
            SET @ss =
            'UPDATE ##db_name SET ' + @d2 + ' = ''' + @d3 + ''' WHERE LoginUserName = ''' + @d1 + '''' 
            EXEC (@ss)
            ;
        END 
        ELSE 
        BEGIN 
            DECLARE
            @var NVARCHAR(MAX)
            ,@ParmDefinition NVARCHAR(MAX)
            ,@var1 NVARCHAR(MAX)
            ;
            SET @var =
            N'SELECT @var1 = ' + QUOTENAME(@d2) + ' FROM ##db_name WHERE LoginUserName = ''' + @d1 + ''''
            ; 
            SET @ParmDefinition =
            N'@var1 NVARCHAR(600) OUTPUT '
            ; 
            EXECUTE Sp_executesql @var,@ParmDefinition,@var1 = @var1 OUTPUT
            ;
            SET @var1 =
            ISNULL(@var1, ' ')
            ;
            SET @var =
            '  UPDATE ##db_name SET ' + @d2 + '=''' + @var1 + ' ' + @d3 + ''' WHERE LoginUserName = ''' + @d1 + '''  '
            ;
            EXEC (@var)
            ;
        END
        FETCH NEXT FROM cursPermisTable INTO @d1,@d2,@d3
    END 
    CLOSE cursPermisTable
    ;
    DEALLOCATE cursPermisTable 
    ;
    UPDATE ##db_name SET
    IsSysAdminLogin = 'Y'
    FROM
    ##db_name TT
    INNER JOIN
    dbo.syslogins SL
        ON TT.LoginUserName = SL.[name]
    WHERE
    SL.sysadmin = 1
    ;
    DECLARE cursDNamesAsColumns CURSOR FAST_FORWARD LOCAL FOR
    SELECT [name]
    FROM tempdb.sys.columns
    WHERE
    OBJECT_ID = OBJECT_ID('tempdb..##db_name')
    AND [name] NOT IN ('LoginUserName','IsEmptyRow')
    ORDER BY [name]
    ;
    OPEN cursDNamesAsColumns
    ;
    DECLARE 
    @ColN VARCHAR(255)
    ,@tSQLText NVARCHAR(4000)
    ;
    FETCH NEXT FROM cursDNamesAsColumns INTO @ColN
    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @tSQLText =
N'UPDATE ##db_name SET
IsEmptyRow = ''N''
WHERE IsEmptyRow IS NULL
AND ' + QUOTENAME(@ColN) + ' IS NOT NULL
;
'

        --PRINT @tSQLText ;
        EXEC sp_executesql @tSQLText ;
    FETCH NEXT FROM cursDNamesAsColumns INTO @ColN
    END
    CLOSE cursDNamesAsColumns
    ;
    DEALLOCATE cursDNamesAsColumns
    ;
    UPDATE ##db_name SET
    IsEmptyRow = 'Y'
    WHERE IsEmptyRow IS NULL
    ;
    UPDATE ##db_name SET
    IsSysAdminLogin = 'N'
    FROM
    ##db_name TT
    INNER JOIN
    dbo.syslogins SL
        ON TT.LoginUserName = SL.[name]
    WHERE
    SL.sysadmin = 0
    ;
    SELECT * FROM ##db_name
    ;
    DROP TABLE ##db_name
    ;
    DROP TABLE #permission
    ;
END TRY
BEGIN CATCH
    DECLARE
    @cursDBs_Status INT
    ,@cursSysSrvPrinName_Status INT
    ,@cursDatabases_Status INT
    ,@cursPermisTable_Status INT
    ,@cursDNamesAsColumns_Status INT
    ;
    SELECT
    @cursDBs_Status = CURSOR_STATUS('GLOBAL','cursDBs')
    ,@cursSysSrvPrinName_Status = CURSOR_STATUS('GLOBAL','cursSysSrvPrinName')
    ,@cursDatabases_Status = CURSOR_STATUS('GLOBAL','cursDatabases')
    ,@cursPermisTable_Status = CURSOR_STATUS('GLOBAL','cursPermisTable')
    ,@cursDNamesAsColumns_Status = CURSOR_STATUS('GLOBAL','cursPermisTable')
    ;
    IF @cursDBs_Status > -2
        BEGIN
            CLOSE cursDBs ;
            DEALLOCATE cursDBs ;
        END
    IF @cursSysSrvPrinName_Status > -2
        BEGIN
            CLOSE cursSysSrvPrinName ;
            DEALLOCATE cursSysSrvPrinName ;
        END
    IF @cursDatabases_Status > -2
        BEGIN
            CLOSE cursDatabases ;
            DEALLOCATE cursDatabases ;
        END
    IF @cursPermisTable_Status > -2
        BEGIN
            CLOSE cursPermisTable ;
            DEALLOCATE cursPermisTable ;
        END
    IF @cursDNamesAsColumns_Status > -2
        BEGIN
            CLOSE cursDNamesAsColumns ;
            DEALLOCATE cursDNamesAsColumns ;
        END
    SELECT ErrorNum = ERROR_NUMBER(),ErrorMsg = ERROR_MESSAGE() ;
END CATCH
GO
/*
EXEC [master].dbo.uspGetPermissionsOfAllLogins_DBsOnColumns ;
*/

Method:2SCRIPT TO LIST ALL SQL SERVER LOGINS, SERVER ROLES AND DATABASE ROLES
Here is the useful script to list all the SQL server Logins and roles.  This script is useful in auditing


--#### LOGINS,USERS,ROLES (LUR) SCRIPT ####
--#### SCRIPT TESTED IN SQL SERVER 2000,2005,2008 AND 2008R2####
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
-- CREATING A TEMP TABLE TO LOAD WITH DATABASE ROLES
CREATE TABLE [TEMPDB].[DBO].[DB_ROLES]( 
      [DBNAME] [SYSNAME] , 
      [USERNAME] [SYSNAME] , 
      [DB_OWNER] [VARCHAR](3) , 
      [DB_ACCESSADMIN] [VARCHAR](3) , 
      [DB_SECURITYADMIN] [VARCHAR](3) , 
      [DB_DDLADMIN] [VARCHAR](3) , 
      [DB_DATAREADER] [VARCHAR](3) , 
      [DB_DATAWRITER] [VARCHAR](3) , 
      [DB_DENYDATAREADER] [VARCHAR](3) , 
      [DB_DENYDATAWRITER] [VARCHAR](3) , 
      [DT_CREATE] [DATETIME] NOT NULL, 
      [DT_UPDATE] [DATETIME] NOT NULL, 
      [DT_REPORT] [DATETIME] NOT NULL CONSTRAINT [DF__DBROLES__CUR_DAT__3A179ED3]  DEFAULT (GETDATE()) 
) ON [PRIMARY] 
GO 
INSERT INTO    [TEMPDB].[DBO].[DB_ROLES] 
EXEC SP_MSFOREACHDB 
'     SELECT  
    ''?'' AS DBNAME,
      USERNAME, 
     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, 
       CREATEDATE, 
     UPDATEDATE, 
       GETDATE() 
       FROM ( 
       SELECT B.NAME AS USERNAME, C.NAME AS ROLENAME, B.CREATEDATE, B.UPDATEDATE 
            FROM   
                    [?].DBO.SYSMEMBERS A   JOIN [?].DBO.SYSUSERS  B  ON A.MEMBERUID = B.UID 
                        JOIN [?].DBO.SYSUSERS C ON A.GROUPUID = C.UID  
                        )S    
               GROUP BY USERNAME, CREATEDATE, UPDATEDATE 
         ORDER BY USERNAME' 
-- RETRIVE LOGINS, USERS AND ROLES TOGETHER 
SELECT  SERVERPROPERTY('SERVERNAME') AS [SERVERNAME], 
B.NAME AS [LOGINNAME], 
CASE B.SYSADMIN  WHEN '1' THEN 'YES' ELSE 'NO' END AS SYSADMIN, 
CASE B.SECURITYADMIN WHEN '1' THEN 'YES' ELSE 'NO' END AS SECURITYADMIN, 
CASE B.SETUPADMIN  WHEN '1' THEN 'YES' ELSE 'NO' END AS SETUPADMIN, 
CASE B.PROCESSADMIN  WHEN '1' THEN 'YES' ELSE 'NO' END AS PROCESSADMIN, 
CASE B.DISKADMIN  WHEN '1' THEN 'YES' ELSE 'NO' END AS DISKADMIN, 
CASE B.DBCREATOR  WHEN '1' THEN 'YES' ELSE 'NO' END AS DBCREATOR, 
CASE B.BULKADMIN  WHEN '1' THEN 'YES' ELSE 'NO' END AS BULKADMIN, 
B.DBNAME AS [DEFAULT_DBNAME], 
A.* INTO #LOGINS FROM [TEMPDB].[DBO].[DB_ROLES] A RIGHT JOIN MASTER..SYSLOGINS B ON A.USERNAME=B.NAME 
--WHERE B.ISNTUSER=1  
--INCLUDE TO EXCLUDE THE SQL LOGINS 
SELECT * FROM #LOGINS ORDER BY [LOGINNAME] 
DROP TABLE  [TEMPDB].[DBO].[DB_ROLES] 
DROP TABLE  #LOGINS 
--------------SCRIPT END-----------------------













Method:3  SQL Server List Login's Database 
in the security audit of our server was to review the login's database role membership and server role membership. 

There is a system stored procedure sp_helpsrvrolemember to list server role membership of login, but it is difficult to review the result of this procedure . Below script will give us the server role membership in more readable format.By copying the the result to Excel, it is more easy to review the server role membership of logins. More over system store procedure will not list the logins which have control server permission.
WITH CTE_Role (name,role,type_desc)
AS
(SELECT PRN.name,
srvrole.name AS [role] , 
Prn.Type_Desc 
FROM sys.server_role_members membership 
INNER JOIN (SELECT * FROM sys.server_principals  WHERE type_desc='SERVER_ROLE') srvrole 
ON srvrole.Principal_id= membership.Role_principal_id 
RIGHT JOIN sys.server_principals  PRN 
ON PRN.Principal_id= membership.member_principal_id WHERE Prn.Type_Desc NOT IN ('SERVER_ROLE') AND PRN.is_disabled =0

UNION ALL

SELECT p.[name], 'ControlServer' ,p.type_desc AS loginType FROM sys.server_principals p 
  JOIN sys.server_permissions Sp
   ON p.principal_id = sp.grantee_principal_id WHERE sp.class = 100 
  AND sp.[type] = 'CL' 
  AND state = 'G' )
SELECT 
name,
Type_Desc ,
CASE WHEN [public]=1 THEN 'Y' ELSE 'N' END AS 'Public',
CASE WHEN [sysadmin] =1 THEN 'Y' ELSE 'N' END AS 'SysAdmin' ,
CASE WHEN [securityadmin] =1 THEN 'Y' ELSE 'N' END AS 'SecurityAdmin',
CASE WHEN [serveradmin] =1 THEN 'Y' ELSE 'N' END AS 'ServerAdmin',
CASE WHEN [setupadmin] =1 THEN 'Y' ELSE 'N' END AS 'SetupAdmin',
CASE WHEN [processadmin] =1 THEN 'Y' ELSE 'N' END AS 'ProcessAdmin',
CASE WHEN [diskadmin] =1 THEN 'Y' ELSE 'N' END AS 'DiskAdmin',
CASE WHEN [dbcreator] =1 THEN 'Y' ELSE 'N' END AS 'DBCreator',
CASE WHEN [bulkadmin] =1 THEN 'Y' ELSE 'N' END AS 'BulkAdmin' ,
CASE WHEN [ControlServer] =1 THEN 'Y' ELSE 'N' END AS 'ControlServer' 
FROM CTE_Role 
PIVOT(
COUNT(role) FOR role IN ([public],[sysadmin],[securityadmin],[serveradmin],[setupadmin],[processadmin],[diskadmin],[dbcreator],[bulkadmin],[ControlServer])

) AS pvt WHERE Type_Desc NOT IN ('SERVER_ROLE')ORDER BY name,type_desc 

;
go












Method:4  SQL Server : Server Level Role Permission 


The system stored procedure sp_helprolemember  will help us to list the login's database role membership (system defined and user defined database role membership),but this will work only on database level. To get membership across all databases in an instance, this procedure has to run in each databases. The below script will list all users in all  databases and its database role membership.




SET NOCOUNT ON

CREATE TABLE #DatabaseRoleMemberShip 
   (
        Username VARCHAR(100),
        Rolename VARCHAR(100),
        Databasename VARCHAR(100)
         
    )DECLARE @Cmd AS VARCHAR(MAX)DECLARE @PivotColumnHeaders VARCHAR(4000)           SET @Cmd = 'USE [?] ;insert into #DatabaseRoleMemberShip 
select u.name,r.name,''?'' from sys.database_role_members RM inner join 
sys.database_principals U on U.principal_id=RM.member_principal_id
inner join sys.database_principals R on R.principal_id=RM.role_principal_id
where u.type<>''R'''EXEC sp_MSforeachdb @command1=@cmd

SELECT  @PivotColumnHeaders =                         
  COALESCE(@PivotColumnHeaders + ',[' + CAST(rolename AS VARCHAR(MAX)) + ']','[' + CAST(rolename AS VARCHAR(MAX))+ ']'                     
  )                     
  FROM (SELECT DISTINCT rolename FROM #DatabaseRoleMemberShip )a ORDER BY rolename  ASC


SET @Cmd = 'select 
databasename,username,'+@PivotColumnHeaders+'
from 
(
select   * from #DatabaseRoleMemberShip) as p
pivot 
(
count(rolename  )
for rolename in ('+@PivotColumnHeaders+') )as pvt'EXECUTE(@Cmd )        DROP TABLE #DatabaseRoleMemberShip 

go













Method:5  SQL Server : Find Culprit CONTROL SERVER permission 

Today morning I was working on one of the server to complete the regular documentation of security audit with details of logins with sysadmin rights and their access to various database. Later I have realized that, one login has access to all database but it is not captured by my auditing script. Here comes the culprit , the CONTROL SERVER rights.

By default CONTROL SERVER rights is equivalent to sysadmin permission except the fact that logins with CONTROL SERVER rights will honor the explicitly denied server level permission where the members of sysadmin server role bypass the explicitly denied server level permission. Also note that logins with control server permission will have implicit access to the databases like the sysadmin members and database owners. 

Logins with control server rights will not have mapping entry in the sys.database_principals but it will have access to all databases. The worst part is, logins with CONROL SERVER permission are not easy to find out unless you prepare explicit query. It is not listed in the UI of  SSMS or there is no system procedure like sp_helpsrvrolemember to list the logins with CONTROL SERVER  right.

Let us walk through a sample script. Create two logins using the below script
CREATE login SysadminLogin WITH password ='password123~'
GO
CREATE login controlserverlogin WITH password ='password123~'
GO
EXEC sp_addsrvrolemember 'SysadminLogin','sysadmin'
GO
GRANT control server TO controlserverlogin 
Now log in to the server using the controlserverlogin  and you can access all the databases and perform any actions. Let us see what will happen on explicitly denying the server level permission.

DENY VIEW ANY DATABASE TO controlserverlogin
GO
DENY VIEW ANY DATABASE TO sysadminlogin
GO


Now log in to the server using both the login. You can notice that, in the session that connected with the controlserverlogin will list only Master and Tempdb databases while the session connected with sysadminlogin will list all available databases.
The other potential issue with logins having control server right is , they can add them self  to the sysadmin server role or can create a new login with membership to the sysadmin server role. Fortunately it is not possible to do it in straight forward steps. Let us see how it will work .Connect to the server using the  controlserverlogin and  execute the below scripts
/* Fortunately  this will fail */ 
EXEC sp_addsrvrolemember 'controlserverlogin','sysadmin';
GO
/* Unfortunately  this will work even if sa account is disabled*/
EXECUTE AS LOGIN = 'sa';
GO
EXEC sp_addsrvrolemember 'controlserverlogin','sysadmin';
GO
REVERT; 


Now the controlserverlogin has sysadmin role membership and you can see all available databases.

Below script  list the logins with sysadmin role membership and control server permission.

/* List login with membership to Sysadmin server role and Control Server right*/
SELECT p.[name] [Login], 'sysadmin Role Member' [Access] ,p.type_desc AS loginTypeFROM sys.server_principals p
  JOIN sys.server_role_members RM
   ON p.principal_id = rm.member_principal_id 
  JOIN sys.server_principals rp 
   ON rm.role_principal_id = rp.principal_id WHERE rp.NAME = 'sysadmin' UNION ALL SELECT p.[name], 'Control Server Right' ,p.type_desc AS loginTypeFROM sys.server_principals p 
  JOIN sys.server_permissions Sp
   ON p.principal_id = sp.grantee_principal_id WHERE sp.class = 100 
  AND sp.[type] = 'CL' 
  AND state = 'G' GO
















Below script will help to list the explicitly denied server level permission for logins which have control server permission.
/* List Explicitly denied permission for the login that has control server permission*/
SELECT CSL.*,sp.permission_name [Explicitly Denied Permission]  FROM sys.server_permissions  SP INNER JOIN (SELECT p.principal_id ,p.[name], p.type_desc AS loginTypeFROM sys.server_principals p 
  JOIN sys.server_permissions Sp
   ON p.principal_id = sp.grantee_principal_id WHERE sp.class = 100 
  AND sp.[type] = 'CL' 
  AND state = 'G' ) CSL ON CSL.principal_id = sp.grantee_principal_id
  WHERE state ='D'




Method:6  SQL Server : Script to List All SQL Server Database Users and Their Access Rights into a Flat File 


I have a task to produce a hard copy of all database users and each access rights of all SQL Server servers. I would like to run this once a week in batch. Is this achievable?

You can do this in many ways. If you are creating a BAT file, you can save the following code and then use your BAT to execute it. It has two option commented in the code to use a single database or all databases on the target server you enter in the BAT. I have included a sample of the batch at the bottom:



--Example of batch file to execute 
--sqlcmd -S YOURTARGETSERVERHERE -E -i "C:\YOURPATHTOSQLSCRIPT\USERPERMISSIONS.SQL&qu ot; -s , -o "C:\YOURPATHHERE\UserPermisions.CSV"

Save the following files as "USERPERMISSIONS.SQL"

else run directly.

set nocount on 
declare @permission table ( 
Database_Name sysname, 
User_Role_Name sysname, 
Account_Type nvarchar(60), 
Action_Type nvarchar(128), 
Permission nvarchar(60), 
ObjectName sysname null, 
Object_Type nvarchar(60) 

declare @dbs table (dbname sysname) 
declare @Next sysname 
insert into @dbs 
--Use this for all databases 
select name from sys.databases order by name 
--Uncomment and use this for a single DB 
--select name from 'YOURDBNAMEHERE' as dbname 
select top 1 @Next = dbname from @dbs 
while (@@rowcount<>0) 
begin 
insert into @permission 
exec('use [' + @Next + '] 
declare @objects table (obj_id int, obj_type char(2)) 
insert into @objects 
select id, xtype from master.sys.sysobjects 
insert into @objects 
select object_id, type from sys.objects 

SELECT ''' + @Next + ''', a.name as ''User or Role Name'', a.type_desc as ''Account Type'', 
d.permission_name as ''Type of Permission'', d.state_desc as ''State of Permission'', 
OBJECT_SCHEMA_NAME(d.major_id) + ''.'' + object_name(d.major_id) as ''Object Name'', 
case e.obj_type 
when ''AF'' then ''Aggregate function (CLR)'' 
when ''C'' then ''CHECK constraint'' 
when ''D'' then ''DEFAULT (constraint or stand-alone)'' 
when ''F'' then ''FOREIGN KEY constraint'' 
when ''PK'' then ''PRIMARY KEY constraint'' 
when ''P'' then ''SQL stored procedure'' 
when ''PC'' then ''Assembly (CLR) stored procedure'' 
when ''FN'' then ''SQL scalar function'' 
when ''FS'' then ''Assembly (CLR) scalar function'' 
when ''FT'' then ''Assembly (CLR) table-valued function'' 
when ''R'' then ''Rule (old-style, stand-alone)'' 
when ''RF'' then ''Replication-filter-procedure'' 
when ''S'' then ''System base table'' 
when ''SN'' then ''Synonym'' 
when ''SQ'' then ''Service queue'' 
when ''TA'' then ''Assembly (CLR) DML trigger'' 
when ''TR'' then ''SQL DML trigger'' 
when ''IF'' then ''SQL inline table-valued function'' 
when ''TF'' then ''SQL table-valued-function'' 
when ''U'' then ''Table (user-defined)'' 
when ''UQ'' then ''UNIQUE constraint'' 
when ''V'' then ''View'' 
when ''X'' then ''Extended stored procedure'' 
when ''IT'' then ''Internal table'' 
end as ''Object Type'' 
FROM [' + @Next + '].sys.database_principals a 
left join [' + @Next + '].sys.database_permissions d on a.principal_id = d.grantee_principal_id 
left join @objects e on d.major_id = e.obj_id 
order by a.name, d.class_desc') 
delete @dbs where dbname = @Next 
select top 1 @Next = dbname from @dbs 
end 
set nocount off 
select * from @permission















Method:7  SQL Server : Object rights


create table ##ObjRights(
    [ServerName] nvarchar(128), [DatabaseName] nvarchar(128), [Owner] nvarchar(128), [Object] nvarchar(128), [Grantee] nvarchar(128), [Grantor] nvarchar(128), [ProtectType] nvarchar(20), [Action] nvarchar(50), [Column] nvarchar(4000));

declare dbs cursor for select name from master.dbo.sysdatabases;
declare @db nvarchar(128)
        ,@sql varchar(500);
open dbs;
fetch dbs into @db;
while @@fetch_status=0
begin
    set @sql = 'use '+@db+';
    insert ##ObjRights ([Owner], [Object], [Grantee], [Grantor], [ProtectType], [Action], [Column])
    exec sp_helprotect;
    update ##ObjRights set DatabaseName='''+@db+'''
    where DatabaseName is null;'
    exec(@sql);
    fetch dbs into @db;
end
close dbs;
deallocate dbs;
update ##ObjRights set ServerName= case when convert(nvarchar, serverproperty('InstanceName')) is null
        then convert(nvarchar, serverproperty('MachineName'))
    else convert(nvarchar, serverproperty('MachineName'))+N'/'+convert(nvarchar, serverproperty('InstanceName')) end;
select * from ##ObjRights;
drop table ##ObjRights;
go 



SQL USERS Completed Reference -Part2