Stored Procedure to list out user access

Is there a built in stored procedure that would allow me to list out
the database permissions assigned to a particular user or role?

Re: Stored Procedure to list out user access

SQL Server (alderran666@gmail.com) writes:
> Is there a built in stored procedure that would allow me to list out
> the database permissions assigned to a particular user or role?

Not really. Rather you are best off querying the view
sys.database_permissions. You can also use fn_my_permissions and
has_perms_by_name, but you would first need to impersonate the user in
question.

All the above applies to SQL 2005 only. There were big overhaul of
security in SQL 2005.

Make it a habit to always specify which version of SQL Server you are using
when you ask questions.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: Stored Procedure to list out user access

On Jan 19, 5:51=A0pm, Erland Sommarskog wrote:
> SQL Server (alderran...@gmail.com) writes:
> > Is there a built in stored procedure that would allow me to list out
> > the database permissions assigned to a particular user or role?
>
> Not really. Rather you are best off querying the view
> sys.database_permissions. You can also use fn_my_permissions and
> has_perms_by_name, but you would first need to impersonate the user in
> question.
>
> All the above applies to SQL 2005 only. There were big overhaul of
> security in SQL 2005.
>
> Make it a habit to always specify which version of SQL Server you are usin=
g
> when you ask questions.
>
> --
> Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
>
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodte=
chnol/sql/2005/downloads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/p=
reviousversions/books.mspx

It's SQL 2005... Thanks!