Blog Post

What Permissions Does This Login Have?

,

I recently was tasked with this ticket:

Please add new login Domain\Bob to server MyServer.  Grant the login the same permissions as Domain\Mary.

On the face of it, this seems relatively straightforward, right?  It is the kind of request that we all get from time to time, whether as an ad-hoc task or as part of a larger project, such as a migration.

The catch of course is that it isn't that easy - how do you know what permissions Mary has?

  • Is Mary a member of any server-level roles?
  • What specific individual server permissions does she have?
  • What database(s) is she a member of?  What database role(s) is she in?
  • What specific object(s) does she have permissions to? (this is often the killer)
Each of these items can be manually checked, of course - you can browse to Security>>Logins and right-click on Mary to see what server-level roles she is in and which databases she has access to, and then you can browse to each database and check what roles she is in and which objects she has explicit permissions on....
Sounds like fun, right?
As with all things SQL, almost anything you can do via the GUI can also be done programmatically (although it can be ugly).  
If you read my blog with any regularity you know that I am a firm believer in building on the work of others while granting credit where due - that is, there is no need to reinvent the wheel when you have a freely available example to start with.  Over time I have compiled links in my personal store to code created by other people for each of the components of the task at hand:
Each of these gave me part of the answer to my question about Mary, but why not roll them all together?  After some brief digging I couldn't find someone who had taken that next step.
I took the code from each of the three sources above (thanks Kendal, Phillip, and Wayne!) and modified them to play nicely together, including wrapping them in sp_msforeachdb as relevant and adding WHERE clauses to filter for an individual login.
I considered what I might use this code for, and then took one further step - I took the code from sp_help_revlogin (thanks Microsoft!) and added it to the start of my new code block.  This allows me to script the login itself at the beginning in case I want to transfer the login and its permissions to a new server (or replace them if something goes wrong on the original server!)
--
In my Bob and Mary case, I ran the code, and ran a find/replace for Mary>>Bob - this gave me a script to create Domain\Bob and grant him all of the same role memberships and permissions as Domain\Mary.
One note - if you run this for SQL logins (rather than Windows logins) you have the issue of the login SID (Security Identifier).  That is one of the big bonuses of using sp_help_revlogin is that it scripts out the SID of the login, so that when you try to re-create that login it maintains its SID for all security chains.  Of course for what we are doing in this example you wouldn't want that - if Mary and Bob were SQL logins, you would want to take one additional step in the final script of editing the CREATE LOGIN statement to remove the @sid parameter (since your new login Bob couldn't use Mary's SID),  
--
The resulting script takes an input parameter of a login name (or NULL) and then outputs CREATE/GRANT/etc. commands for the login, role memberships, and object permissions of that login (or of all logins if NULL is specified).
  • First, sp_help_revlogin (and sp_hexadecimal) is created if it doesn't exist - if it does exist it is ALTER'ed with this recent version
  • sp_help_revlogin is run to generate a CREATE LOGIN
  • Kendal's code (slightly modified) is run to generate sp_addsrvrolemember  and GRANT/DENY statements for server-level security
  • Wayne's code (again modified) is run to generate CREATE USER statements for the databases
  • Phillip's code (modified) is run to generate sp_AddRoleMember statements for database roles
  • Finally, another piece of Wayne's code (from that same post) is run to generate the GRANT/DENY statements for individual database object permissions
The current version of the script (v 1.0) is located here. http://tiny.cc/PermScript
A few caveats - because I include the CREATE/ALTER PROC for sp_help_revlogin and sp_hexadecimal in my script, my actual parameter (@LoginName) to specify your desired login is roughly halfway down the script (after the CREATE/ALTER PROC code) - the easiest way to deal with this is to CTRL-F for "SET @LoginName"
The code outputs via a large number of result sets, so I *strongly* recommend you change your query output from Grid to Text before you run the script.
I have run this against versions from 2005 through 2014 and it has generated successfully.
I am working on improving commenting and error handling. but check it out and let me know what you think and any holes you find (and as with all things on the Web, run it at your own risk).
Hope this helps!

--

NOTE - someone mentioned in a comment that they had trouble accessing the link - I originally didn't publish the script in the blog because the length was prohibitive but here it is:

--

/*

Permissions Scripter v1.0

All code on the web should be examined and run at your own risk!

--

2015/12/16
Andy Galbraith @DBA_ANDY
http://nebraskasql.blogspot.com/

--

IMPORTANT - CTRL-F for 'SET @LoginName' and set the name

Strongly recommend you change the Query output from Grid to Text for best output results

--

Uses code from several sources that I have combined and modifed to work together.

Sources are attributed thoughout but are also noted here:

**  Microsoft - sp_help_revlogin - https://support.microsoft.com/en-us/kb/918992

**  Kendal Van Dyke @SQLDBA - "Scripting Server Permissions And Role Assignments" - http://www.kendalvandyke.com/2009/01/scripting-server-permissions-and-role.html

**  Phillip Kelley - "Generating scripts for database role membership in SQL Server 2005"
http://stackoverflow.com/questions/3265526/generating-scripts-for-database-role-membership-in-sql-server-2005

**  Wayne Sheffield @DBAWayne - "script out database users for the selected database" - http://www.sqlservercentral.com/Forums/Topic977700-146-1.aspx


*/


SET NOCOUNT ON

SELECT 'SET NOCOUNT ON;'+CHAR(13)+'USE [MASTER];'+CHAR(13)+'GO'+CHAR(13) as '/* Set Database Context to master */'



USE [master]
GO

/*
Microsoft - sp_help_revlogin - https://support.microsoft.com/en-us/kb/918992
*/

/****** Object:  StoredProcedure [dbo].[sp_hexadecimal]    Script Date: 10/12/2010 13:58:44 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'sp_hexadecimal')
EXEC ('CREATE PROC dbo.sp_hexadecimal AS SELECT GETDATE()')
GO

ALTER PROCEDURE [dbo].[sp_hexadecimal]
@binvalue varbinary(256),
@hexvalue varchar(256) OUTPUT
AS
DECLARE @charvalue varchar(256)
DECLARE @i int
DECLARE @length int
DECLARE @hexstring char(16)
SELECT @charvalue = '0x'
SELECT @i = 1
SELECT @length = DATALENGTH (@binvalue)
SELECT @hexstring = '0123456789ABCDEF'
WHILE (@i <= @length)
BEGIN
DECLARE @tempint int
DECLARE @firstint int
DECLARE @secondint int
SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
SELECT @firstint = FLOOR(@tempint/16)
SELECT @secondint = @tempint - (@firstint*16)
SELECT @charvalue = @charvalue +
SUBSTRING(@hexstring, @firstint+1, 1) +
SUBSTRING(@hexstring, @secondint+1, 1)
SELECT @i = @i + 1
END
SELECT @hexvalue = @charvalue

GO


/****** Object:  StoredProcedure [dbo].[sp_help_revlogin]    Script Date: 10/12/2010 13:58:51 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'sp_help_revlogin')
EXEC ('CREATE PROC dbo.sp_help_revlogin AS SELECT GETDATE()')
GO

ALTER PROCEDURE [dbo].[sp_help_revlogin] @login_name sysname = NULL AS
DECLARE @name sysname
DECLARE @type varchar (1)
DECLARE @hasaccess int
DECLARE @denylogin int
DECLARE @is_disabled int
DECLARE @PWD_varbinary  varbinary (256)
DECLARE @PWD_string  varchar (514)
DECLARE @SID_varbinary varbinary (85)
DECLARE @SID_string varchar (514)
DECLARE @tmpstr  varchar (MAX)
DECLARE @is_policy_checked varchar (3)
DECLARE @is_expiration_checked varchar (3)

DECLARE @defaultdb sysname

IF (@login_name IS NULL)
  DECLARE login_curs CURSOR FOR

      SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM
sys.server_principals p LEFT JOIN sys.syslogins l
      ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name <> 'sa'
ELSE
  DECLARE login_curs CURSOR FOR


      SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM
sys.server_principals p LEFT JOIN sys.syslogins l
      ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name = @login_name
OPEN login_curs

FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
IF (@@fetch_status = -1)
BEGIN
  PRINT 'No login(s) found.'
  CLOSE login_curs
  DEALLOCATE login_curs
  RETURN -1
END
SET @tmpstr = '/* sp_help_revlogin script ** Generated ' + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
PRINT @tmpstr
PRINT ''
WHILE (@@fetch_status <> -1)
BEGIN
  IF (@@fetch_status <> -2)
  BEGIN
    PRINT ''
    SET @tmpstr = '/* Login: ' + @name+' */'
    PRINT @tmpstr
    IF (@type IN ( 'G', 'U'))
    BEGIN -- NT authenticated account/group

      SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS WITH DEFAULT_DATABASE = [' + @defaultdb + ']'
    END
    ELSE BEGIN -- SQL Server authentication
        -- obtain password and sid
            SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, 'PasswordHash' ) AS varbinary (256) )
        EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT
        EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT

        -- obtain password policy state
        SELECT @is_policy_checked = CASE is_policy_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name
        SELECT @is_expiration_checked = CASE is_expiration_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name

            SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' WITH PASSWORD = ' + @PWD_string + '
HASHED, SID = ' + @SID_string + ', DEFAULT_DATABASE = [' + @defaultdb + ']'

        IF ( @is_policy_checked IS NOT NULL )
        BEGIN
          SET @tmpstr = @tmpstr + ', CHECK_POLICY = ' + @is_policy_checked
        END
        IF ( @is_expiration_checked IS NOT NULL )
        BEGIN
          SET @tmpstr = @tmpstr + ', CHECK_EXPIRATION = ' + @is_expiration_checked
        END
    END
    IF (@denylogin = 1)
    BEGIN -- login is denied access
      SET @tmpstr = @tmpstr + '; DENY CONNECT SQL TO ' + QUOTENAME( @name )
    END
    ELSE IF (@hasaccess = 0)
    BEGIN -- login exists but does not have access
      SET @tmpstr = @tmpstr + '; REVOKE CONNECT SQL TO ' + QUOTENAME( @name )
    END
    IF (@is_disabled = 1)
    BEGIN -- login is disabled
      SET @tmpstr = @tmpstr + '; ALTER LOGIN ' + QUOTENAME( @name ) + ' DISABLE'
    END
    PRINT @tmpstr
  END

  FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
   END
CLOSE login_curs
DEALLOCATE login_curs


RETURN 0


GO

DECLARE @LoginName sysname

/*

SET LOGIN NAME!!!!

*/


SET @LoginName = 'sa'

/*
For all Logins set to NULL

SET @LoginName = NULL
*/


EXEC sp_help_revlogin @login_name = @LoginName

PRINT ' '

/*
Kendal Van Dyke @SQLDBA - "Scripting Server Permissions And Role Assignments" - http://www.kendalvandyke.com/2009/01/scripting-server-permissions-and-role.html

Modifed by me to filter for an individual login if one is specified
*/

/* Generate statements to create server permissions for SQL logins, Windows Logins, and Groups */

-- Role Members
IF @LoginName is NOT NULL
SELECT  'EXEC sp_addsrvrolemember @rolename =' + SPACE(1)
        + QUOTENAME(usr1.name, '''') + ', @loginame =' + SPACE(1)
        + QUOTENAME(usr2.name, '''') +';' AS '/* Server Role Memberships */'
FROM    sys.server_principals AS usr1
        INNER JOIN sys.server_role_members AS rm ON usr1.principal_id = rm.role_principal_id
        INNER JOIN sys.server_principals AS usr2 ON rm.member_principal_id = usr2.principal_id
and usr2.name = @LoginName
ORDER BY rm.role_principal_id ASC
ELSE
SELECT  'EXEC sp_addsrvrolemember @rolename =' + SPACE(1)
        + QUOTENAME(usr1.name, '''') + ', @loginame =' + SPACE(1)
        + QUOTENAME(usr2.name, '''') +';' AS '/* Server Role Memberships */'
FROM    sys.server_principals AS usr1
        INNER JOIN sys.server_role_members AS rm ON usr1.principal_id = rm.role_principal_id
        INNER JOIN sys.server_principals AS usr2 ON rm.member_principal_id = usr2.principal_id

-- Permissions
IF @LoginName is NOT NULL
SELECT  server_permissions.state_desc COLLATE SQL_Latin1_General_CP1_CI_AS
        + ' ' + server_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS
        + ' TO [' + server_principals.name COLLATE SQL_Latin1_General_CP1_CI_AS
        + '];' AS '/* Server Level Permissions */'
FROM    sys.server_permissions AS server_permissions WITH ( NOLOCK )
        INNER JOIN sys.server_principals AS server_principals WITH ( NOLOCK ) ON server_permissions.grantee_principal_id = server_principals.principal_id
WHERE   server_principals.type IN ( 'S', 'U', 'G' )
and server_principals.name = @LoginName
ORDER BY server_principals.name,
        server_permissions.state_desc,
        server_permissions.permission_name
ELSE
SELECT  server_permissions.state_desc COLLATE SQL_Latin1_General_CP1_CI_AS
        + ' ' + server_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS
        + ' TO [' + server_principals.name COLLATE SQL_Latin1_General_CP1_CI_AS
        + '];' AS '/* Server Level Permissions */'
FROM    sys.server_permissions AS server_permissions WITH ( NOLOCK )
        INNER JOIN sys.server_principals AS server_principals WITH ( NOLOCK ) ON server_permissions.grantee_principal_id = server_principals.principal_id
WHERE   server_principals.type IN ( 'S', 'U', 'G' )
ORDER BY server_principals.name,
        server_permissions.state_desc,
        server_permissions.permission_name


/*
Wayne Sheffield @DBAWayne - "script out database users for the selected database" - http://www.sqlservercentral.com/Forums/Topic977700-146-1.aspx

Modified by me to run inside sp_msforeachdb and to filter for an individual login if one is specified
Also added COLLATE DATABASE_DEFAULT statements to handle databases with collations different from the instance
*/


DECLARE @strsql nvarchar(4000)

PRINT '/* Database Users */'
IF @LoginName is not NULL
set @strsql = 'SELECT ''/* ? */'';SELECT ''USE [?];
GO
IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = '' +
QuoteName(dp.name, char(39)) COLLATE DATABASE_DEFAULT +        '')
CREATE USER '' + QuoteName(dp.name) +
       IsNull('' FOR LOGIN '' + QuoteName(sp.name),'''') +
       IsNull('' WITH DEFAULT_SCHEMA = '' + QuoteName(dp.default_schema_name),'''') + '';''
  FROM [?].sys.database_principals dp
       LEFT JOIN [?].sys.server_principals sp
         ON sp.sid = dp.sid
 WHERE dp.type like ''[GUS]''
and dp.name = '''+@LoginName+''''
ELSE
set @strsql = 'SELECT ''/* ? */'';SELECT ''USE [?];
GO
IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = '' +
QuoteName(dp.name, char(39)) COLLATE DATABASE_DEFAULT +        '')
CREATE USER '' + QuoteName(dp.name) +
       IsNull('' FOR LOGIN '' + QuoteName(sp.name),'''') +
       IsNull('' WITH DEFAULT_SCHEMA = '' + QuoteName(dp.default_schema_name),'''') + '';''
  FROM [?].sys.database_principals dp
       LEFT JOIN [?].sys.server_principals sp
         ON sp.sid = dp.sid
 WHERE dp.type like ''[GUS]'''


EXEC sp_msforeachdb @strsql


/*
Phillip Kelley - "Generating scripts for database role membership in SQL Server 2005"
http://stackoverflow.com/questions/3265526/generating-scripts-for-database-role-membership-in-sql-server-2005


Modified by me to run inside sp_msforeachdb and to filter for an individual login if one is specified
*/

PRINT '/* Database Role Memberships */'

IF @LoginName is not NULL
set @strsql = 'SELECT ''/* ? */'';
SELECT ''USE [?];
GO
EXECUTE sp_AddRoleMember '''''' + roles.name + '''''', '''''' + users.name + ''''''''+'';''
 from [?].sys.database_principals users
  inner join [?].sys.database_role_members link
   on link.member_principal_id = users.principal_id
  inner join [?].sys.database_principals roles
   on roles.principal_id = link.role_principal_id
where users.name = '''+@LoginName+''''
ELSE
set @strsql = 'SELECT ''/* ? */'';
SELECT ''USE [?];
GO
EXECUTE sp_AddRoleMember '''''' + roles.name + '''''', '''''' + users.name + ''''''''+'';''
 from [?].sys.database_principals users
  inner join [?].sys.database_role_members link
   on link.member_principal_id = users.principal_id
  inner join [?].sys.database_principals roles
   on roles.principal_id = link.role_principal_id'

EXEC sp_msforeachdb @strsql


/*
Wayne Sheffield @DBAWayne - "script out database users for the selected database" - http://www.sqlservercentral.com/Forums/Topic977700-146-1.aspx

Modified by me to run inside sp_msforeachdb and to filter for an individual login if one is specified
*/


PRINT '/* Database Object Permissions */'

IF @LoginName is not NULL
set @strsql = 'SELECT ''/* ? */'';
SELECT ''USE [?];
GO
''+ dp.state_desc COLLATE SQL_Latin1_General_CP1_CI_AS + '' '' +
       dp.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS +
       '' ON '' + QuoteName(ss.name) + ''.'' + QuoteName(so.name) +
       '' TO '' + QuoteName(dp2.name) + '';''+CHAR(13)
  FROM [?].sys.database_permissions dp
       JOIN [?].sys.database_principals dp2
         ON dp2.principal_id = dp.grantee_principal_id
       JOIN [?].sys.objects so
         ON so.object_id = dp.major_id
       JOIN [?].sys.schemas ss
         ON ss.schema_id = so.schema_id
WHERE dp2.name  = '''+@LoginName+''''
ELSE
set @strsql = 'SELECT ''/* ? */'';
SELECT ''USE [?];
GO
''+ dp.state_desc COLLATE SQL_Latin1_General_CP1_CI_AS + '' '' +
       dp.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS +
       '' ON '' + QuoteName(ss.name) + ''.'' + QuoteName(so.name) +
       '' TO '' + QuoteName(dp2.name) + '';''+CHAR(13)
  FROM [?].sys.database_permissions dp
       JOIN [?].sys.database_principals dp2
         ON dp2.principal_id = dp.grantee_principal_id
       JOIN [?].sys.objects so
         ON so.object_id = dp.major_id
       JOIN [?].sys.schemas ss
         ON ss.schema_id = so.schema_id'

EXEC sp_msforeachdb @strsql


PRINT '/* END OF SCRIPT */'

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

You rated this post out of 5. Change rating

Share

Share

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

You rated this post out of 5. Change rating

Related content

Technical Article

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

You rated this post out of 5. Change rating

2009-02-23

1,567 reads

Technical Article

Networking - Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I'd like to talk about social networking. We'll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let...

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

You rated this post out of 5. Change rating

2009-02-17

1,530 reads

Technical Article

Speaking at Community Events - More Thoughts

Last week I posted Speaking at Community Events - Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I've got a few more thoughts on the topic this week, and I look forward to your comments.

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

You rated this post out of 5. Change rating

2009-02-13

360 reads