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, |
|
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 |
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
go
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
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
OR
(user_access = 2 and exists (SELECT * FROM #srvrole WHERE ServerRole in (‘dbcreator’,‘sysadmin’)))
OR
(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
