Query permissions on objects for roles?

How do I query the sys tables in a database to find what objects have what permissions for a given role? A SELECT statement is desired, and I'd like to avoid using sp_ stored procedures, but mention of a few to look at would be appreciated.
Thanks to anyone who can help.Here is part of a script I wrote for restoring server and database configuration. It creates an sql script for restoring user and role permissions. It uses the bitwise & operator to decode values in the sypermissions table. If it's not exactly what you are looking for, you can deconstruct it to see how to pull the data you need.

blindman
-------
set nocount on
select '--OBJECT LEVEL PERMISSIONS'
select 'GRANT SELECT ON [' + owners.name + '].[' + sysobjects.name + '] TO [' + sysusers.name + ']' + CHAR(13) + 'GO' + CHAR(13)
from sysusers owners
inner join sysobjects on owners.uid = sysobjects.uid
inner join syspermissions on sysobjects.id = syspermissions.id
inner join sysusers on syspermissions.grantee = sysusers.uid
where 1 & syspermissions.actadd > 0
go
select 'DENY SELECT ON [' + owners.name + '].[' + sysobjects.name + '] TO [' + sysusers.name + ']' + CHAR(13) + 'GO' + CHAR(13)
from sysusers owners
inner join sysobjects on owners.uid = sysobjects.uid
inner join syspermissions on sysobjects.id = syspermissions.id
inner join sysusers on syspermissions.grantee = sysusers.uid
where 1 & syspermissions.actmod > 0
go
select 'GRANT UPDATE ON [' + owners.name + '].[' + sysobjects.name + '] TO [' + sysusers.name + ']' + CHAR(13) + 'GO' + CHAR(13)
from sysusers owners
inner join sysobjects on owners.uid = sysobjects.uid
inner join syspermissions on sysobjects.id = syspermissions.id
inner join sysusers on syspermissions.grantee = sysusers.uid
where 2 & syspermissions.actadd > 0
go
select 'DENY UPDATE ON [' + owners.name + '].[' + sysobjects.name + '] TO [' + sysusers.name + ']' + CHAR(13) + 'GO' + CHAR(13)
from sysusers owners
inner join sysobjects on owners.uid = sysobjects.uid
inner join syspermissions on sysobjects.id = syspermissions.id
inner join sysusers on syspermissions.grantee = sysusers.uid
where 2 & syspermissions.actmod > 0
go
select 'GRANT REFERENCES ON [' + owners.name + '].[' + sysobjects.name + '] TO [' + sysusers.name + ']' + CHAR(13) + 'GO' + CHAR(13)
from sysusers owners
inner join sysobjects on owners.uid = sysobjects.uid
inner join syspermissions on sysobjects.id = syspermissions.id
inner join sysusers on syspermissions.grantee = sysusers.uid
where 4 & syspermissions.actadd > 0
go
select 'DENY REFERENCES ON [' + owners.name + '].[' + sysobjects.name + '] TO [' + sysusers.name + ']' + CHAR(13) + 'GO' + CHAR(13)
from sysusers owners
inner join sysobjects on owners.uid = sysobjects.uid
inner join syspermissions on sysobjects.id = syspermissions.id
inner join sysusers on syspermissions.grantee = sysusers.uid
where 4 & syspermissions.actmod > 0
go
select 'GRANT INSERT ON [' + owners.name + '].[' + sysobjects.name + '] TO [' + sysusers.name + ']' + CHAR(13) + 'GO' + CHAR(13)
from sysusers owners
inner join sysobjects on owners.uid = sysobjects.uid
inner join syspermissions on sysobjects.id = syspermissions.id
inner join sysusers on syspermissions.grantee = sysusers.uid
where 8 & syspermissions.actadd > 0
go
select 'DENY INSERT ON [' + owners.name + '].[' + sysobjects.name + '] TO [' + sysusers.name + ']' + CHAR(13) + 'GO' + CHAR(13)
from sysusers owners
inner join sysobjects on owners.uid = sysobjects.uid
inner join syspermissions on sysobjects.id = syspermissions.id
inner join sysusers on syspermissions.grantee = sysusers.uid
where 8 & syspermissions.actmod > 0
go
select 'GRANT DELETE ON [' + owners.name + '].[' + sysobjects.name + '] TO [' + sysusers.name + ']' + CHAR(13) + 'GO' + CHAR(13)
from sysusers owners
inner join sysobjects on owners.uid = sysobjects.uid
inner join syspermissions on sysobjects.id = syspermissions.id
inner join sysusers on syspermissions.grantee = sysusers.uid
where 16 & syspermissions.actadd > 0
go
select 'DENY DELETE ON [' + owners.name + '].[' + sysobjects.name + '] TO [' + sysusers.name + ']' + CHAR(13) + 'GO' + CHAR(13)
from sysusers owners
inner join sysobjects on owners.uid = sysobjects.uid
inner join syspermissions on sysobjects.id = syspermissions.id
inner join sysusers on syspermissions.grantee = sysusers.uid
where 16 & syspermissions.actmod > 0
go
select 'GRANT EXECUTE ON [' + owners.name + '].[' + sysobjects.name + '] TO [' + sysusers.name + ']' + CHAR(13) + 'GO' + CHAR(13)
from sysusers owners
inner join sysobjects on owners.uid = sysobjects.uid
inner join syspermissions on sysobjects.id = syspermissions.id
inner join sysusers on syspermissions.grantee = sysusers.uid
where 32 & syspermissions.actadd > 0
go
select 'DENY EXECUTE ON [' + owners.name + '].[' + sysobjects.name + '] TO [' + sysusers.name + ']' + CHAR(13) + 'GO' + CHAR(13)
from sysusers owners
inner join sysobjects on owners.uid = sysobjects.uid
inner join syspermissions on sysobjects.id = syspermissions.id
inner join sysusers on syspermissions.grantee = sysusers.uid
where 32 & syspermissions.actmod > 0
go|||I would suggest looking at sp_helprotect , you may be able to borrow code from it and my code below to do what you want. Also, look at the view INFORMATION_SCHEMA.TABLE_PRIVILEGES in master DB.

Tim S

Here's my sproc to script permissions:

CREATE proc dbo.adm_script_permissions
(
@.name ncharacter varying(776) = NULL,
@.username sysname = NULL,
@.grantorname sysname = NULL,
@.permissionarea character varying(10) = 'o s'
)AS
BEGIN

SET NOCOUNT ON

CREATE Table #tmp_protect_data ( OwnerName sysname NOT Null, ObjectName sysname NOT Null, GranteeName sysname NOT Null,
GrantorName sysname NOT Null, ProtectType char(10) Null, ActionName varchar(20) Null, ColumnName sysname Null)

SET @.username = PARSENAME ( @.username , 1 ) -- Remove []

insert into #tmp_protect_data (OwnerName, ObjectName, GranteeName, GrantorName, ProtectType, ActionName, ColumnName )
exec sp_helprotect @.name = @.name, @.username = @.username, @.grantorname = @.grantorname, @.permissionarea = @.permissionarea

SELECT RTRIM(ProtectType) + ' ' + ActionName +
CASE
WHEN ColumnName IN ('.','(All+New)','(All)','(New)') THEN ''
ELSE ' (' + ColumnName + ')'
END +
' ON ' + OwnerName + '.' + ObjectName + ' TO ' + GranteeName + char(13)+char(10)
FROM #tmp_protect_data
WHERE ColumnName IS NOT NULL AND -- ColumnName is NULL on deleted columns
OwnerName <> 'INFORMATION_SCHEMA' AND ObjectName NOT LIKE 'dt[_]%' AND ObjectName NOT IN ('dtproperties')
ORDER BY OwnerName + '.' + ObjectName, GranteeName, ActionName, RTRIM(ProtectType)

DROP Table #tmp_protect_data

SET NOCOUNT OFF

END

Query Notification - checking permissions

Hi all,

I am looking at replacing a polled system with Query Notification. However when I create the SqlDependency I need to be sure I have the correct permissions. I check the SqlClientPermissions via the Demand() method, but also want to ensure I have the correct DB permission given my current connection string. As I understand it I need to have the following permissions:

CREATE PROCEDURE, QUEUE, and SERVICE permissions
SUBSCRIBE QUERY NOTIFICATIONS
SELECT on underlying tables
RECEIVE on QueryNotificationErrorsQueue

I check most of these via the 'has_perms_by_name' function, but cannot find the correct syntax to check for RECEIVE on QueryNotificationErrorsQueue. I would also love to find a way to do this via SMO instead of issuing SQl commands. Also am I missing any checks ....

Finally, I have also run into the problem whereby SQL issues the following error:

The activated proc [dbo].[SqlQueryNotificationStoredProcedure-1fd90369-7781-4bad-a1b7-e1b56e328374] running on queue ImlHostDB.dbo.SqlQueryNotificationService-1fd90369-7781-4bad-a1b7-e1b56e328374 output the following: 'Could not obtain information about Windows NT group/user 'EMEA\DyerN', error code 0x54b.'

I ran into this issue when I bought my machine out of sleep mode with it no longer connected to the network. Is their no way to get a error notification. In this situation I will just stop seeing notification and without looing at the ErrorLog believe their is nothing wrong.

Many Thanks, Nick

The SQL Server instance in question cannot communicate with the Active Directory to validate the Windows accounts (like EMEA\DyerN) and hits error 0x54b, which is ERROR_NO_SUCH_DOMAIN. You should contact your network/security administrator to diagnose and troubleshoot the problem, as is unrelated to Service Broker or Query Notifications (are you working on a laptop disconnected from domain by any chance?) Alternatively, you could use SQL users/logins instead of Windows users/logins.|||

This was true, I was disconnected from the domain. My hope however was for the query notification to fire signally that an error had occurred and change notifcations could not be delivered. This would allow me to handle it programatically and re-register with different credentials or fall back to polling for data changes.

Currently as it stands if I get disconnected from the domain my query notifications will simply stop and I will receive no notification that an error has occurred. This does not leave me with a robust solution.

Thanks, Nick

BTW An idea on determine if I have the correct permissions to RECEIVE on QueryNotificationErrorsQueue ?

|||

NickUk wrote:

BTW An idea on determine if I have the correct permissions to RECEIVE on QueryNotificationErrorsQueue ?

select * from sys.fn_my_permissions('dbo.QueryNotificationErrorsQueue','object')

|||

Query Notifications run 'execute as owner' so if a database is owned by a domain account, query notifications fail in the event a domain control cannot be contacted

Basically, change owner to SA to avoid the issue.

See this writeup

http://aspadvice.com/blogs/ssmith/archive/2006/11/06/SqlDependency-Issue-Resolved.aspx

Query Notification - checking permissions

Hi all,

I am looking at replacing a polled system with Query Notification. However when I create the SqlDependency I need to be sure I have the correct permissions. I check the SqlClientPermissions via the Demand() method, but also want to ensure I have the correct DB permission given my current connection string. As I understand it I need to have the following permissions:

CREATE PROCEDURE, QUEUE, and SERVICE permissions
SUBSCRIBE QUERY NOTIFICATIONS
SELECT on underlying tables
RECEIVE on QueryNotificationErrorsQueue

I check most of these via the 'has_perms_by_name' function, but cannot find the correct syntax to check for RECEIVE on QueryNotificationErrorsQueue. I would also love to find a way to do this via SMO instead of issuing SQl commands. Also am I missing any checks ....

Finally, I have also run into the problem whereby SQL issues the following error:

The activated proc [dbo].[SqlQueryNotificationStoredProcedure-1fd90369-7781-4bad-a1b7-e1b56e328374] running on queue ImlHostDB.dbo.SqlQueryNotificationService-1fd90369-7781-4bad-a1b7-e1b56e328374 output the following: 'Could not obtain information about Windows NT group/user 'EMEA\DyerN', error code 0x54b.'

I ran into this issue when I bought my machine out of sleep mode with it no longer connected to the network. Is their no way to get a error notification. In this situation I will just stop seeing notification and without looing at the ErrorLog believe their is nothing wrong.

Many Thanks, Nick

The SQL Server instance in question cannot communicate with the Active Directory to validate the Windows accounts (like EMEA\DyerN) and hits error 0x54b, which is ERROR_NO_SUCH_DOMAIN. You should contact your network/security administrator to diagnose and troubleshoot the problem, as is unrelated to Service Broker or Query Notifications (are you working on a laptop disconnected from domain by any chance?) Alternatively, you could use SQL users/logins instead of Windows users/logins.|||

This was true, I was disconnected from the domain. My hope however was for the query notification to fire signally that an error had occurred and change notifcations could not be delivered. This would allow me to handle it programatically and re-register with different credentials or fall back to polling for data changes.

Currently as it stands if I get disconnected from the domain my query notifications will simply stop and I will receive no notification that an error has occurred. This does not leave me with a robust solution.

Thanks, Nick

BTW An idea on determine if I have the correct permissions to RECEIVE on QueryNotificationErrorsQueue ?

|||

NickUk wrote:

BTW An idea on determine if I have the correct permissions to RECEIVE on QueryNotificationErrorsQueue ?

select * from sys.fn_my_permissions('dbo.QueryNotificationErrorsQueue','object')

|||

Query Notifications run 'execute as owner' so if a database is owned by a domain account, query notifications fail in the event a domain control cannot be contacted

Basically, change owner to SA to avoid the issue.

See this writeup

http://aspadvice.com/blogs/ssmith/archive/2006/11/06/SqlDependency-Issue-Resolved.aspx