Re-generating SQL Server Logins - Part 3

April 12, 2011

In previous articles in this series, we we have seen how to regenerate logins with properties, if the login is member of a server-level role, belongs to a database and has a database role, and we also covered server and database securable class. We will wrap up this series by drilling through schema, object and column level permission.

Object level permission

Object level permissions are the finest level permission in SQL Server, and it can be difficult to get a comprehensive list of what privilege a login possesses, either granted or denied. In this article, we will be covering object-level permission for these database securables:

· Assembly

· Asymmetric Keys

· Certificates

· Full-text

o Catalog

o Stoplist

· Object or column

o Table

o Table-Valued Function

o View

o Stored Procedure

o Extended Stored Procedure

o Aggregate Function

· Schema

· Service Broker

o Contract

o Message Tyoe

o Remote Service

o Binding

o Route

o Service

· Symmetric Keys

· Type

· XML Schema Collection

In the table below, I have outlined what permission can be granted/denied for each securable object:

Database Securable object	Available permission
Assembly	Control, Take Ownership, Alter, Reference, View Definition
Asymmetric Keys	Control, Take Ownership, Alter, Reference, View Definition
Certificate	Control, Take Ownership, Alter, Reference, View Definition
FullText – Catalog	Control, Take Ownership, Alter, Reference, View Definition
FullText – Stoplist	Control, Take Ownership, Alter, Reference, View Definition
Object - Scarlar Function	Execute, Reference
Object - Table Valued function	Delete, Insert, Reference, Select, Update
Object - Stored Procedure	Execute
Object - Table	Delete, Insert, Reference, Select, Update
Object – View	Delete, Insert, Reference, Select, Update
Schema	Take ownerhip, Alter, Execute, Insert, Delete, Update, Select, References, View Change Tracking, View Definition
Service Broker – Contract	Control, Take Ownership, Alter, Reference, View Definition
Service Broker - Message Type	Control, Take Ownership, Alter, Reference, View Definition
Service Broker - Remote Service Binding	Control, Take Ownership, Alter, View Definition
Service Broker –Route	Control, Take Ownership, Alter, View Definition
Service Broker –Service	Control, Take Ownership, Send, Alter, View Definition
Symmetric Key	Alter, Control, Reference, Take Ownership, View Definition
Type	Control, Execute, Reference, Take Ownership, View Definition
Users - Application Role	Control, Alter, View Definition
Users - Database Role	Control, Take Ownership, Alter, View Definition
Users - Database User	Control, Impersonate, Alter, View Definition
XML Schema Collection	Alter, Control, Execute, Reference, Take Ownership, View Definition

Permission inheritance

You can grant or deny object permission for the database user, database role and application role. You can give object level permission with the "WITH GRANT OPTION" keyword; this will give the ability to grant permission to another principal. If principal A has gained object level permissions through principal B who is now being denied permission with CASCADE, then principal A will be denied permission as well.

Generate script for object level permission

Now, we can look into the script. To use the script, here are the steps. This script works on SQL 2005, SQL 2008 and SQL 2008 R2.

1. Open Microsoft SQL Server Management Studio (a.k.a SSMS), make a connection to the SQL instance, then click New Query. Make sure the credential you use to run the code has adequate privilege to generate the script.

2. Copy below code into the query window.

3. Replace 'YourloginName' with the login you would like to re-generate the securable permission script, then execute.

----------------------------------------------

--Login Pre-requisites

----------------------------------------------

set concat_null_yields_null off

USE master

SET NOCOUNT ON

DECLARE @login_name varchar(100)

SET @login_name = 'YourLoginName'

-----------------------------------------------------------------

IF lower(@login_name) IN ('sa','public')

BEGIN

RAISERROR (15405,11,1,@login_name)

RETURN

END

IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = @login_name AND type IN ('G','U','S'))

BEGIN

PRINT 'Please input valid login name'

RETURN

END

DECLARE @login_sid varbinary(85)

SELECT @login_sid = sid FROM sys.server_principals WHERE name = @login_name

DECLARE @maxid int

IF OBJECT_ID('tempdb..#db_users') is not null

BEGIN

DROP TABLE #db_users

END

SELECT id = identity(int,1,1), sql_cmd = 'SELECT '''+name+''', * FROM ['+name+'].sys.database_principals' INTO #db_users FROM sys.databases

WHERE state_desc <> 'OFFLINE'

SELECT @maxid = @@ROWCOUNT

----------------------------------------------

--Create Server Role Temp table

----------------------------------------------

IF OBJECT_ID('tempdb..#srvrole') IS NOT NULL

BEGIN

DROP TABLE #srvrole

END

CREATE TABLE #srvrole(ServerRole sysname, MemberName sysname, MemberSID varbinary(85))

INSERT INTO [#srvrole] EXEC sp_helpsrvrolemember

DECLARE @login_srvrole varchar(1000)

SET @login_srvrole = ''

IF EXISTS (select * from [#srvrole] where ServerRole = 'sysadmin' AND MemberName = @login_name)

BEGIN

PRINT '--Login ['+@login_name+'] is part of sysadmin server role, hence possesses full privileges for SQL instance: '+@@servername

PRINT 'GO'

SELECT @login_srvrole = @login_srvrole + 'EXEC sp_addsrvrolemember '''+MemberName+''','''+ServerRole+''''+CHAR(10) FROM #srvrole

WHERE [MemberName] = @login_name

PRINT @login_srvrole

RETURN

END

---------------------------------------------------

--Find out list of db that the login has access to

---------------------------------------------------

PRINT ''

PRINT '----------------------------------------------'

PRINT '--Create database user for login '

PRINT '----------------------------------------------'

IF OBJECT_ID('tempdb..#alldb_users') is not null

BEGIN

DROP TABLE #alldb_users

END

CREATE TABLE #alldb_users(

[dbname] [sysname] NOT NULL,

[name] [sysname] NOT NULL,

[principal_id] [int] NOT NULL,

[type] [char](1) NOT NULL,

[type_desc] [nvarchar](60) NULL,

[default_schema_name] [sysname] NULL,

[create_date] [datetime] NOT NULL,

[modify_date] [datetime] NOT NULL,

[owning_principal_id] [int] NULL,

[sid] [varbinary](85) NULL,

[is_fixed_role] [bit] NOT NULL

)

DECLARE @id int, @sqlcmd varchar(500)

SET @id = 1

WHILE @id <=@maxid

BEGIN

SELECT @sqlcmd = sql_cmd FROM #db_users WHERE id = @id

INSERT INTO #alldb_users EXEC (@sqlcmd)

SET @id = @id + 1

END

DELETE FROM #alldb_users WHERE sid is null

DELETE FROM #alldb_users WHERE sid <> @login_sid

IF NOT EXISTS (SELECT * FROM #alldb_users )

BEGIN

PRINT '--Login ['+@login_name+'] doesnt have access to any database'

END

DECLARE @name sysname, @dbname sysname, @schema sysname, @dbuser_cmd varchar(200)

DECLARE dbuser_cursor CURSOR FOR

SELECT dbname, name, default_schema_name

FROM #alldb_users

OPEN dbuser_cursor

FETCH NEXT FROM dbuser_cursor INTO @dbname, @name, @schema

WHILE @@FETCH_STATUS = 0

BEGIN

IF @schema IS NOT NULL

BEGIN

SELECT @dbuser_cmd = 'USE ['+dbname+']

IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = '''+name+''')

BEGIN

CREATE USER ['+@name+'] FOR LOGIN ['+@login_name+']'+isnull(' WITH DEFAULT_SCHEMA=['+default_schema_name+']','')+'

END

' FROM #alldb_users WHERE name = @name and dbname = @dbname

END

ELSE

BEGIN

SELECT @dbuser_cmd = 'USE ['+dbname+']

IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = '''+name+''')

BEGIN

CREATE USER ['+@name+'] FOR LOGIN ['+@login_name+']

END

' FROM #alldb_users WHERE name = @name and dbname = @dbname

END

print @dbuser_cmd

FETCH NEXT FROM dbuser_cursor INTO @dbname, @name, @schema

END

CLOSE dbuser_cursor

DEALLOCATE dbuser_cursor

----------------------------------------------

--granting database role to login

----------------------------------------------

IF OBJECT_ID('tempdb..#dbrole') is not null

BEGIN

DROP TABLE #dbrole

END

create table #dbrole (id int identity(1,1), dbname varchar(100), dbrole varchar (100), dbrole_member varchar(100), sid varbinary(85), default_schema_name varchar(100), login_name varchar(100), db_principal_id int)

DECLARE @dbrole_sqlcmd varchar(max)

DECLARE dbrole_cursor CURSOR FAST_FORWARD FOR

SELECT

'SELECT '''+dbname+''', c.name, b.name, b.sid, b.default_schema_name, d.name, b.principal_id as login_name

from ['+dbname+'].sys.database_role_members a

inner join ['+dbname+'].sys.database_principals b on a.member_principal_id = b.principal_id

inner join ['+dbname+'].sys.database_principals c on a.role_principal_id = c.principal_id

left join sys.server_principals d on b.sid = d.sid

where d.name= '''+@login_name+''''

from #alldb_users

OPEN dbrole_cursor

FETCH NEXT FROM dbrole_cursor INTO @dbrole_sqlcmd

WHILE @@FETCH_STATUS = 0

BEGIN

INSERT INTO #dbrole (dbname, dbrole, dbrole_member, sid, default_schema_name, login_name, db_principal_id) exec(@dbrole_sqlcmd)

FETCH NEXT FROM dbrole_cursor INTO @dbrole_sqlcmd

END

CLOSE dbrole_cursor

DEALLOCATE dbrole_cursor

DELETE FROM #dbrole WHERE sid <> @login_sid

IF EXISTS (SELECT * FROM #dbrole where dbrole = 'db_owner')

BEGIN

PRINT '----------------------------------------------'

PRINT'--Login is db_owner of below databases'

PRINT'----------------------------------------------'

END

DECLARE @dbname_dbowner varchar(100), @dbrole_member varchar(100)

DECLARE dbowner_cursor CURSOR FAST_FORWARD FOR

SELECT dbname, dbrole_member from #dbrole where dbrole = 'db_owner'

OPEN dbowner_cursor

FETCH NEXT FROM dbowner_cursor INTO @dbname_dbowner, @dbrole_member

WHILE @@FETCH_STATUS = 0

BEGIN

PRINT 'USE ['+@dbname_dbowner+']

EXEC sp_addrolemember ''db_owner'','''+@dbrole_member +'''

GO'

FETCH NEXT FROM dbowner_cursor INTO @dbname_dbowner, @dbrole_member

END

CLOSE dbowner_cursor

DEALLOCATE dbowner_cursor

--------------------------------------------------------------------------------------------------------

--Find out what database the login has permission to access (avoid restricted and single user database)

--------------------------------------------------------------------------------------------------------

DELETE From #srvrole where MemberName <> @login_name

IF OBJECT_ID('tempdb..#alldb_users_access') IS NOT NULL

BEGIN

DROP TABLE #alldb_users_access

END

SELECT a.*, collation_name INTO #alldb_users_access FROM #alldb_users a inner join sys.databases b ON a.dbname = b.name

WHERE user_access = 0

(user_access = 2 and exists (SELECT * FROM #srvrole WHERE ServerRole in ('dbcreator','sysadmin')))

(user_access = 2 and a.dbname in (SELECT dbname FROM #dbrole WHERE dbrole = 'db_owner' AND login_name = @login_name))

--SELECT * FROM #alldb_users_access

--------------------------------------------------------------------------------------------------------

--Remove database that login doesnt have permission to connect

--------------------------------------------------------------------------------------------------------

IF OBJECT_ID('tempdb..#dbconnect') is not null

BEGIN

DROP TABLE #dbconnect

END

CREATE TABLE #dbconnect ( dbname varchar(100), connect_status bit)

DECLARE @dbconnect_sqlcmd varchar(1000)

SET @dbconnect_sqlcmd = ''

DECLARE dbbconnect_cursor CURSOR FAST_FORWARD FOR

SELECT 'select distinct '''+dbname+''', 1 from ['+dbname+'].sys.database_permissions a

inner join ['+dbname+'].sys.database_principals b on a.grantee_principal_id = b.principal_id

inner join ['+dbname+'].sys.server_principals c on b.sid = c.sid

where c.name = '''+@login_name+''''

from #alldb_users_access

OPEN dbbconnect_cursor

FETCH NEXT FROM dbbconnect_cursor INTO @dbconnect_sqlcmd

WHILE @@FETCH_STATUS = 0

BEGIN

INSERT INTO #dbconnect exec( @dbconnect_sqlcmd)

FETCH NEXT FROM dbbconnect_cursor INTO @dbconnect_sqlcmd

END

CLOSE dbbconnect_cursor

DEALLOCATE dbbconnect_cursor

insert into #dbconnect

select a.dbname, 0 from #alldb_users_access a left join #dbconnect b on a.dbname = b.dbname

where b.dbname is null

delete from #alldb_users_access where dbname not in (select dbname from #dbconnect where connect_status= 1 )

delete from #alldb_users_access where dbname in (select dbname from #dbrole where dbrole = 'db_owner')

---------------------------------------------------------------------------------------

-- Grant object level permission

----------------------------------------------------------------------------------------

--select USER_NAME(), SUSER_NAME()

----------------------------------------------

--database object permission to login

----------------------------------------------

PRINT ''

PRINT '--------------------------------------------------------------'

PRINT '--Grant/Revoking/Denying database object permission to login'

PRINT '---------------------------------------------------------------'

--SELECT * FROM #securable_class WHEREsqlcmd like '%OBJECT%'

--EXECUTE AS Login = 'BrennanJJ'

--SELECT 'AuDB2',* FROM [AuDB2].sys.fn_my_permissions ('AccountHandles', 'OBJECT');

--Revert

DECLARE @sql_objpermission varchar(8000)

SET @sql_objpermission = ''

IF OBJECT_ID('tempdb..#objpermission') IS NOT NULL

BEGIN

DROP TABLE #objpermission

END

CREATE TABLE #objpermission(

[dbname] [sysname] NOT NULL,

[obj_name] [nvarchar](128) NULL,

--[user_name] [nvarchar](128) NULL,

[class] [tinyint] NOT NULL,

[class_desc] [nvarchar](60) NULL,

[major_id] [int] NOT NULL,

[minor_id] [int] NOT NULL,

[grantee_principal_id] [int] NOT NULL,

[grantor_principal_id] [int] NOT NULL,

[type] [char](4) NOT NULL,

[permission_name] [nvarchar](128) NULL,

[state] [char](1) NOT NULL,

[state_desc] [nvarchar](60) NULL,

[dbuser] [nvarchar](128) NULL

)

DECLARE objlevel_cursor CURSOR FOR

SELECT distinct --@sql_objpermission = @sql_objpermission +

'SELECT '''+dbname+''' as db_name, ''OBJECT'' COLLATE '+collation_name+' +'' :: [''+d.name+''].[''+b.name+'']'' as obj_name ,a.*, c.name COLLATE '+collation_name+' as dbuser

FROM ['+dbname+'].sys.database_permissions a

inner join ['+dbname+'].sys.sysobjects b on a.major_id = b.id

left join ['+dbname+'].sys.database_principals c on a.grantee_principal_id = c.principal_id

left join ['+dbname+'].sys.sysusers d on b.uid = d.uid

where grantee_principal_id = '+convert(varchar(10),principal_id) + ' AND major_id <> 0 AND minor_id = 0 and class_desc <> ''DATABASE''

' from #alldb_users_access a

--select * from #alldb_users_access

OPEN objlevel_cursor

FETCH NEXT FROM objlevel_cursor INTO @sql_objpermission

WHILE @@FETCH_STATUS = 0

BEGIN

--PRINT @sql_objpermission

--EXEC @sql_objpermission

INSERT INTO #objpermission EXEC (@sql_objpermission)

FETCH NEXT FROM objlevel_cursor INTO @sql_objpermission

END

CLOSE objlevel_cursor

DEALLOCATE objlevel_cursor

--------------------------------------------------------

--New securable types

------------------------------------------------------

if OBJECT_ID('tempdb..#securable') IS NOT NULL

BEGIN

DROP TABLE #securable

END

CREATE TABLE #securable (Category varchar(50), Securable_Type varchar(50), column_name varchar(50), permission_class varchar(50))

--INSERT INTO #securable SELECT 'sysobjects','OBJECT','id'

INSERT INTO #securable SELECT 'assemblies','ASSEMBLY','assembly_id','ASSEMBLY'

INSERT INTO #securable SELECT 'asymmetric_keys','ASYMMETRIC KEY','asymmetric_key_id','ASYMMETRIC_KEY'

INSERT INTO #securable SELECT 'certificates','CERTIFICATE','certificate_id','CERTIFICATE'

INSERT INTO #securable SELECT 'service_contracts','CONTRACT','service_contract_id','SERVICE_CONTRACT'

INSERT INTO #securable SELECT 'fulltext_catalogs','FULLTEXT CATALOG','fulltext_catalog_id','FULLTEXT_CATALOG'

IF @@version like '%SQL Server 2008%'

BEGIN

INSERT INTO #securable SELECT 'fulltext_stoplists','FULLTEXT STOPLIST','stoplist_id','FULLTEXT_CATALOG'

END

INSERT INTO #securable SELECT 'service_message_types','MESSAGE TYPE','message_type_id','MESSAGE_TYPE'

INSERT INTO #securable SELECT 'remote_service_bindings','REMOTE SERVICE BINDING','remote_service_binding_id','REMOTE_SERVICE_BINDING'

INSERT INTO #securable SELECT 'routes','ROUTE','route_id','ROUTE'

INSERT INTO #securable SELECT 'schemas','SCHEMA','schema_id','SCHEMA'

INSERT INTO #securable SELECT 'services','SERVICE','service_id','SERVICE'

INSERT INTO #securable SELECT 'types','TYPE','user_type_id','TYPE'

INSERT INTO #securable SELECT 'xml_schema_collections','XML SCHEMA COLLECTION','xml_collection_id','XML_SCHEMA_COLLECTION'

DECLARE @category varchar(100), @securable_type varchar(50), @col_name varchar(50), @permission_class varchar(50), @sql_securable varchar(max)--, @sql_objpermission2 varchar(max)

DECLARE securable_type_cursor CURSOR FOR

SELECT * FROM #securable

OPEN securable_type_cursor

FETCH NEXT FROM securable_type_cursor INTO @category, @securable_type, @col_name, @permission_class

WHILE @@FETCH_STATUS = 0

BEGIN

DECLARE objlevel_cursor CURSOR FOR

SELECT distinct --@sql_objpermission = @sql_objpermission +

'SELECT '''+dbname+''' as db_name, '''+@securable_type +''' COLLATE '+collation_name+' +'' :: [''+b1.name+'']'' as obj_name,a.*, c.name as dbuser FROM ['+dbname+'].sys.database_permissions a

inner join ['+dbname+'].sys.'+@category+' b1 on a.major_id = b1.'+@col_name+'

left join ['+dbname+'].sys.database_principals c on a.grantee_principal_id = c.principal_id

where grantee_principal_id = '+convert(varchar(10),principal_id) + ' AND major_id <> 0 AND minor_id = 0 and a.class_desc = '''+@permission_class+'''

' from #alldb_users_access a

OPEN objlevel_cursor

FETCH NEXT FROM objlevel_cursor INTO @sql_objpermission

WHILE @@FETCH_STATUS = 0

BEGIN

--PRINT @sql_objpermission

--EXEC(@sql_objpermission)

INSERT INTO #objpermission EXEC (@sql_objpermission)

FETCH NEXT FROM objlevel_cursor INTO @sql_objpermission

END

CLOSE objlevel_cursor

DEALLOCATE objlevel_cursor

FETCH NEXT FROM securable_type_cursor INTO @category, @securable_type, @col_name, @permission_class

END

CLOSE securable_type_cursor

DEALLOCATE securable_type_cursor

---------------------------------------------------------

--Database Principal Securable Handling

---------------------------------------------------------

DELETE FROM #securable

--INSERT INTO #securable SELECT 'sysobjects','OBJECT','id'

INSERT INTO #securable SELECT 'database_principals','USER','principal_id','DATABASE_PRINCIPAL'

INSERT INTO #securable SELECT 'database_principals','ROLE','principal_id','DATABASE_PRINCIPAL'

INSERT INTO #securable SELECT 'database_principals','APPLICATION ROLE','principal_id','DATABASE_PRINCIPAL'

DECLARE securable_type_cursor CURSOR FOR

SELECT * FROM #securable

OPEN securable_type_cursor

FETCH NEXT FROM securable_type_cursor INTO @category, @securable_type, @col_name, @permission_class

WHILE @@FETCH_STATUS = 0

BEGIN

DECLARE objlevel_cursor CURSOR FOR

SELECT distinct --@sql_objpermission = @sql_objpermission +

'SELECT '''+dbname+''' as db_name, CASE b1.type WHEN ''R'' THEN ''ROLE'' WHEN ''A'' THEN ''APPLICATION ROLE'' WHEN ''S'' THEN ''USER'' END COLLATE '+collation_name+' +'' :: [''+b1.name+'']'' as obj_name,a.*, c.name as dbuser FROM ['+dbname+'].sys.database_permissions a

inner join ['+dbname+'].sys.'+@category+' b1 on a.major_id = b1.'+@col_name+'

left join ['+dbname+'].sys.database_principals c on a.grantee_principal_id = c.principal_id

where grantee_principal_id = '+convert(varchar(10),principal_id) + ' AND major_id <> 0 AND minor_id = 0 and a.class_desc = '''+@permission_class+'''

' from #alldb_users_access a

OPEN objlevel_cursor

FETCH NEXT FROM objlevel_cursor INTO @sql_objpermission

WHILE @@FETCH_STATUS = 0

BEGIN

--PRINT @sql_objpermission

--EXEC(@sql_objpermission)

INSERT INTO #objpermission EXEC (@sql_objpermission)

FETCH NEXT FROM objlevel_cursor INTO @sql_objpermission

END

CLOSE objlevel_cursor

DEALLOCATE objlevel_cursor

FETCH NEXT FROM securable_type_cursor INTO @category, @securable_type, @col_name, @permission_class

END

CLOSE securable_type_cursor

DEALLOCATE securable_type_cursor

---------------------------------------------------------

--End of Database Principal Securable Handling

---------------------------------------------------------

--End of New Securable Handling

---------------------------------------------------------

if NOT EXISTS (select 1 from #objpermission )

BEGIN

PRINT '--NO object level permission granted for this login'

END

ELSE

BEGIN

DECLARE @print_objpermission varchar(300)

DECLARE objperm_cursor CURSOR FOR

--SELECT 'use ['+dbname+']'+char(10)+ state_desc +' '+permission_name + ' ON '+ obj_name + CASE state_desc WHEN 'DENY' THEN ' FROM ' ELSE ' TO ' END + '['+dbuser+']' FROM #objpermission WHERE state_desc <> 'GRANT_WITH_GRANT_OPTION' AND class_desc = 'OBJECT_OR_COLUMN'

--UNION

SELECT 'use ['+dbname+']'+char(10)+ state_desc +' '+permission_name + ' ON '+ obj_name + ' TO ['+dbuser+']' FROM #objpermission WHERE state_desc <> 'GRANT_WITH_GRANT_OPTION' --AND class_desc <> 'OBJECT_OR_COLUMN'

UNION

SELECT 'use ['+dbname+']'+char(10)+'GRANT '+permission_name + ' ON '+ obj_name + CASE state_desc WHEN 'DENY' THEN ' FROM ' ELSE ' TO ' END + '['+dbuser+'] WITH GRANT OPTION ' FROM #objpermission WHERE state_desc = 'GRANT_WITH_GRANT_OPTION'

OPEN objperm_cursor

FETCH NEXT FROM objperm_cursor INTO @print_objpermission

WHILE @@FETCH_STATUS = 0

BEGIN

print @print_objpermission

FETCH NEXT FROM objperm_cursor INTO @print_objpermission

END

CLOSE objperm_cursor

DEALLOCATE objperm_cursor

END

--select * from #objpermission

----------------------------------------------

--database column permission to login

----------------------------------------------

PRINT ''

PRINT '--------------------------------------------------------------'

PRINT '--Grant/Revoking/Denying database column permission to login'

PRINT '---------------------------------------------------------------'

DECLARE @sql_colpermission varchar(max)

SET @sql_colpermission = ''

IF OBJECT_ID('tempdb..#colpermission') IS NOT NULL

BEGIN

DROP TABLE #colpermission

END

CREATE TABLE #colpermission(

[dbname] [sysname] NOT NULL,

[col_name] [nvarchar](128) NULL,

[obj_name] [nvarchar](128) NULL,

[obj_owner] [nvarchar](128) NULL,

[class] [tinyint] NOT NULL,

[class_desc] [nvarchar](60) NULL,

[major_id] [int] NOT NULL,

[minor_id] [int] NOT NULL,

[grantee_principal_id] [int] NOT NULL,

[grantor_principal_id] [int] NOT NULL,

[type] [char](4) NOT NULL,

[permission_name] [nvarchar](128) NULL,

[state] [char](1) NOT NULL,

[state_desc] [nvarchar](60) NULL,

[dbuser] [nvarchar](128) NULL

)

--PRINT (@sql_colpermission)

DECLARE collevel_cursor CURSOR FOR

SELECT distinct

'SELECT '''+dbname+''' as db_name, b.name as col_name, c.name as obj_name, e.name as obj_owner, a.*, d.name as dbuser FROM ['+dbname+'].sys.database_permissions a

inner join ['+dbname+'].sys.columns b on a.major_id = b.object_id and a.minor_id = b.column_id

left join ['+dbname+'].sys.sysobjects c on a.major_id = c.id

left join ['+dbname+'].sys.database_principals d on a.grantee_principal_id = d.principal_id

left join ['+dbname+'].sys.sysusers e on c.uid = e.uid

where grantee_principal_id = '+convert(varchar(10),principal_id) + ' AND major_id <> 0 AND major_id> 0 AND minor_id <> 0

' FROM #alldb_users_access

OPEN collevel_cursor

FETCH NEXT FROM collevel_cursor INTO @sql_colpermission

WHILE @@FETCH_STATUS = 0

BEGIN

--INSERT INTO #dblevel (login_name, dbname, entity_name, subentity_name, permission_name) EXEC (@dblevel_sqlcmd)

--PRINT @sql_colpermission

INSERT INTO #colpermission EXEC (@sql_colpermission)

FETCH NEXT FROM collevel_cursor INTO @sql_colpermission

END

CLOSE collevel_cursor

DEALLOCATE collevel_cursor

if NOT EXISTS (select * from #colpermission )

BEGIN

PRINT '--NO column level permission granted for this login'

END

ELSE

BEGIN

DECLARE @print_colpermission varchar(300)

DECLARE colperm_cursor CURSOR FOR

SELECT 'use ['+dbname+']'+char(10)+replace(state_desc,'_',' ')+' '+permission_name + ' ON ['+ obj_owner + '].['+obj_name+ '] ('+ col_name+')'+CASE state_desc WHEN 'DENY' THEN ' FROM ' ELSE ' TO ' END + '['+dbuser+']' FROM #colpermission WHERE state_desc <> 'GRANT_WITH_GRANT_OPTION'

UNION

SELECT 'use ['+dbname+']'+char(10)+'GRANT '+permission_name + ' ON ['+ obj_owner + '].[' + obj_name +'] ('+ col_name+') ' + CASE state_desc WHEN 'DENY' THEN ' FROM ' ELSE ' TO ' END + '['+dbuser+'] WITH GRANT OPTION ' FROM #colpermission WHERE state_desc = 'GRANT_WITH_GRANT_OPTION'

OPEN colperm_cursor

FETCH NEXT FROM colperm_cursor INTO @print_colpermission

WHILE @@FETCH_STATUS = 0

BEGIN

print @print_colpermission

FETCH NEXT FROM colperm_cursor INTO @print_colpermission

END

CLOSE colperm_cursor

DEALLOCATE colperm_cursor

END

--SELECT * FROM #colpermission

If you input a bad login name to the script, it will produce an exception; below are the scenarios.

Scenario #1: If you enter either 'sa'or'public', then you will see a result similar to below snapshot in the SSMS result pane. [Refer Fig 1.0]

Fig 1.1

Scenario #2: If the login you provide doesn’t exists on the SQL instance, then you will see a result similar to below snapshot in the SSMS result pane. [Refer Fig 1.1]

Fig 1.2

Now, we can look into the real scenario about a valid login with permissions on object level securables. By replacing 'YourLoginName' with a valid login, below is the example on how the outcome of the script will look like. It contains the creation of the database user for the login and the permission setting for server and database securable. If a login is db_owner for any database or is part of sysadmin server role member, then the resulting script will only contain the execution of the system stored procedure sp_addrolemember and sp_addsrvrolemember instead of printing out granting securables one by one.

Scenario #3: Input Login is part of sysadmin server role [Refer Fig 1.3]

Fig 1.3

Scenario #4: Input valid login chsu, who has access to three databases and is member of db_owner on AdventureWorksDW, but not for rest of the databases. You will get 4 sections in the result pane. [Refer Fig 1.4, 1.5, 1.6 and 1.7]

Fig 1.4

Fig 1.4 displays that the creation of database user for the input login

Fig 1.5

Fig 1.5 Since this login is a member of db_owner on AdventurWorksDW database, the script will generate the sp_addrolemember command

Fig 1.6

Fig 1.6 displays the object level permission this login has on each database, except for the database the login is a member of the db_owner database role.

Fig 1.7

Fig 1.7 shows that last section of the result pane takes care of the column level permission for the login on each database, except for the database the login is a member of the db_owner database role.

Conclusion

This concludes my series for regenerating SQL Server logins. You can do some tweaking and combine the scripts from all three articles to have a summary from beginning to the end on how a login is defined for a SQL instance. By combining the scripts from the series, you can get the login creation properties, server role and database role definition, server and database securable assignment and granular level object permission defined for the login.

See all articles by Claire Hsu

Re-generating SQL Server Logins - Part 3

Whitepapers and eBooks

Webcasts

Downloads and eKits

Tutorials and Demos