<

Getting User Permissions for SQL Server 2008+

Published on
4,549 Points
549 Views
Last Modified:
Kyle Abrahams
Making the impossible happen by providing creative, simplified solutions to complex problems.
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
0 Comments

Featured Post

Amazon Web Services

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how the fundamental information of how to create a table.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month