Stored Procedure to list out user access

#1

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

#2

SQL Server (alderran666 (AT) gmail (DOT) com) writes:

Quote:

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 (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

#3

On Jan 19, 5:51*pm, Erland Sommarskog <esq... (AT) sommarskog (DOT) se> wrote:

Quote:

SQL Server (alderran... (AT) gmail (DOT) 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, esq... (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

It's SQL 2005... Thanks!

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Stored Procedure to list out user access

comp.databases.ms-sqlserver comp.databases.ms-sqlserver

Stored Procedure to list out user access - 01-19-2008 , 04:28 PM

Re: Stored Procedure to list out user access - 01-19-2008 , 04:51 PM

Re: Stored Procedure to list out user access - 01-19-2008 , 07:17 PM