How to get details of statement / object permissions for a single user 1

[miq]

HI,
Is there a way to get details of select/exec object permissions for a single login/user.

It would be great if this is a possibility so, Thin Client appliciton of mine can be customized according to each user's need without hardcoding there permission details.

Actually, i want to allow users of the application to perform operations on those underlying objects only for which they have permissions. some need to view data only while others can change it as well.

B.R,
miq

 

[hmckillop]

Try this, just replace the username with whatever it is you require. Also you might want to filter on object type and action (i.e. only execute permissions on procedures is all you care about). Best bet then is to join on the sysobjects of your own db and check where xtype = 'P' (procedures) or xtype = 'FN' (functions)

DECLARE @UserName varchar(200)
SET @uSerName = 'MyUser'
select 
	a = o.name, 
	b = user_name(o.uid), 
	user_name(p.uid), 
	p.uid,
	o.sysstat & 0xf, 
	p.id, 
CASE action
WHEN 26  THEN 'REFERENCES'
WHEN 178 THEN 'CREATE FUNCTION'
WHEN 193 THEN 'SELECT'
WHEN 195 THEN 'INSERT'
WHEN 196 THEN 'DELETE'
WHEN 197 THEN 'UPDATE'
WHEN 198 THEN 'CREATE TABLE'
WHEN 203 THEN 'CREATE DATABASE'
WHEN 207 THEN 'CREATE VIEW'
WHEN 222 THEN 'CREATE PROCEDURE'
WHEN 224 THEN 'EXECUTE'
WHEN 228 THEN 'BACKUP DATABASE'
WHEN 233 THEN 'CREATE DEFAULT'
WHEN 235 THEN 'BACKUP LOG'
WHEN 236 THEN 'CREATE RULE'
ELSE '' 
END as action,

action, 
	protecttype 
from dbo.sysprotects p, 
	dbo.sysobjects o,     
	master.dbo.spt_values a 
where 
	o.id = p.id 
	and (( p.action in (193, 197) 
		and ((p.columns & 1) = 1) ) 
		or ( p.action in (195, 196, 224, 26) ))     
		and (convert(tinyint, substring( isnull(p.columns, 0x01), a.low, 1)) & a.high != 0) 
		and a.type = N'P' and a.number = 0  and user_name(p.uid) = @uSerName
		order by a, b

"I'm living so far beyond my income that we may almost be said to be living apart

 

[miq]

HI,
@hmckillop thanks a lot for this valuable snippet. I customized it accordingly

declare @username varchar(50)
set @username = 'guest'
select [object name] = object_name(a.id), [user name] = user_name(a.uid),
a.action, a.protecttype, a.columns from sysprotects a
join sysobjects b on a.id = b.id
where
(a.uid = user_id(@username)and action in ('193', '224') and b.xtype in ('v','u','p'))


B.R,
miq