sql server programming: Getting effective permissions for user/role using SMO -- sql server programming

Getting effective permissions for user/role using SMO

Brad Jones
6/11/2007 6:04:36 PM

sql server programming: Hi,

I need to calculate or get effective permissions on database objects for db
users and
roles using SMO.

If anyone has piece of code for it, thanks a lot!

Regards,
Brad

Re: Getting effective permissions for user/role using SMO

Stefan Delmarco
6/12/2007 1:41:32 AM

Hi Brad,

Unfortunately it doesn't look like SMO supports this out the box. However,
with a little creative coding, this is quite easy to implement. The TSQL
that will return the effective permissions for a login, in the current
database, for the specified object is:

<TSQL>
execute as login = N'unreal\Stefan';
select permission_name AS Permission
FROM fn_my_permissions('dbo.usp_SetDebug', 'object')
order by permission_name;
revert;
</TSQL>

I've attached some C# that allows you to expose this functionality using SMO
objects. More details on fn_my_permissions here:
http://msdn2.microsoft.com/en-us/library/ms176097(SQL.90).aspx

--
Cheers,