Getting User Permissions for SQL Server 2008+

Kyle Abrahams, PMPSenior Director of Technology
CERTIFIED EXPERT
Making the impossible happen by providing creative, simplified solutions to complex problems.
Published:
Updated:
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.

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.

0
1,267 Views
Kyle Abrahams, PMPSenior Director of Technology
CERTIFIED EXPERT
Making the impossible happen by providing creative, simplified solutions to complex problems.

Comments (0)

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.