I recently ran across a situation where I needed to compare permissions between users. After doing some digging on the internet I came across a query that got me something I could use, but I wasn't satisfied with the results.
So after working on it, I thought I would share it with the community in case anyone else needed this for their use.
All the caveats apply in terms of warranties (that is: AS IS).
The original query I found to generate the grant statements was:
SELECT ( dp.state_desc + ' ' + dp.permission_name collate latin1_general_cs_as + ' ON ' + '[' + s.name + ']' + '.' + '[' + o.name + ']' + ' TO ' + '[' + dpr.name + ']' ) AS GRANT_STMT
FROM sys.database_permissions AS dp INNER JOIN sys.objects AS o ON dp.major_id=o.object_id INNER JOIN sys.schemas AS s ON o.schema_id = s.schema_id INNER JOIN sys.database_principals AS dpr ON dp.grantee_principal_id=dpr.principal_id
WHERE dpr.name NOT IN ('public','guest')
From https://stackoverflow.com/questions/1987190/scripting
What this script does is generate the grant scripts for all users on the current database. In converting it to a stored procedure, at first it would only execute on the stored procedure's database. To get around that, we can use a little bit of dynamic SQL and search the transaction locks to determine the database we're calling from.
I then added an option so that it could be run for all databases. Below is the final code for that (A note that I store general stored procedures like this in master as they apply to all databases. This can be changed to any database you like):
/****** Object: StoredProcedure [dbo].[getPermissions] Script Date: 11/28/2017 10:50:37 AM ******/
USE MASTER
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[getPermissions]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[getPermissions]
GO
/****** Object: StoredProcedure [dbo].[getPermissions] Script Date: 11/28/2017 10:50:37 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[getPermissions]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[getPermissions] AS'
END
GO
ALTER procedure [dbo].[getPermissions]
@loginName varchar(100),
@allDatabases int = 0
as
declare @sql varchar(max)
if @allDatabases = 0
begin
set @sql = 'use '
+
-- get the calling database
(SELECT db_name(resource_database_id) FROM sys.dm_tran_locks WHERE request_session_id = @@SPID and resource_type = 'DATABASE' and request_owner_type = 'SHARED_TRANSACTION_WORKSPACE')
+
'; SELECT
(
dp.state_desc + '' '' +
dp.permission_name collate latin1_general_cs_as +
'' ON '' + ''['' + s.name + '']'' + ''.'' + ''['' + o.name + '']'' +
'' TO '' + ''['' + dpr.name + '']''
) AS GRANT_STMT
FROM sys.database_permissions AS dp
INNER JOIN sys.objects AS o ON dp.major_id=o.object_id
INNER JOIN sys.schemas AS s ON o.schema_id = s.schema_id
INNER JOIN sys.database_principals AS dpr ON dp.grantee_principal_id=dpr.principal_id
WHERE dpr.name like ''%' + @loginName + '%''
'
exec (@sql)
end
else
begin
-- for nice outputting.
create table #temp
(DbName varchar(500),
stmnt varchar(8000)
)
set @sql = 'use [?]; SELECT DB_NAME() as DbName,
(
dp.state_desc + '' '' +
dp.permission_name collate latin1_general_cs_as +
'' ON '' + ''['' + s.name + '']'' + ''.'' + ''['' + o.name + '']'' +
'' TO '' + ''['' + dpr.name + '']''
) AS GRANT_STMT
FROM sys.database_permissions AS dp
INNER JOIN sys.objects AS o ON dp.major_id=o.object_id
INNER JOIN sys.schemas AS s ON o.schema_id = s.schema_id
INNER JOIN sys.database_principals AS dpr ON dp.grantee_principal_id=dpr.principal_id
WHERE dpr.name like ''%' + @loginName + '%''
'
insert into #temp
execute sp_MSforeachdb @sql
select * from #temp
drop table #temp
end
GO
and you can call with:
declare @loginName varchar(100) = 'theLogin'
exec master.dbo.getPermissions @loginName
-- OR to get all permissions for all DBs on the server:
declare @loginName varchar(100) = 'theLogin'
exec master.dbo.getPermissions @loginName, 1
Hope it helps someone.
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.
Comments (0)