Query to find permissions..

Hello
I need a query that will show me what permissions a user has in a particular
database. I am not exactly sure how to go about doing that. I know I will
want to specify a database name and user name and based off of that
information for the query to go through and show what kind of permissions
this users has on user objects.
I am using SQL Server 2000.
Please advise..
Thank you,
BrettYou could start with
EXEC databasename..sp_helpuser 'username'
SELECT OBJECT_NAME(id),*
FROM databasename..syspermissions
WHERE grantee=USER_ID('username')
EXEC databasename..sp_msForEachTable 'EXEC sp_MSobjectprivs
@.objname=N''?'',@.grantee=''username'''
In the last output you will see an action column here, it corresponds to
this list, from Books Online
Go|URL: tsqlref.chm::/ts_sys-p_0837.htm
26 = REFERENCES
178 = CREATE FUNCTION
193 = SELECT
195 = INSERT
196 = DELETE
197 = UPDATE
198 = CREATE TABLE
203 = CREATE DATABASE
207 = CREATE VIEW
222 = CREATE PROCEDURE
224 = EXECUTE
228 = BACKUP DATABASE
233 = CREATE DEFAULT
235 = BACKUP LOG
236 = CREATE RULE
"Brett Davis" <bdavis123@.cox.net> wrote in message
news:Oo6rRZsnFHA.3068@.TK2MSFTNGP15.phx.gbl...
> Hello
> I need a query that will show me what permissions a user has in a
> particular database. I am not exactly sure how to go about doing that. I
> know I will want to specify a database name and user name and based off of
> that information for the query to go through and show what kind of
> permissions this users has on user objects.
> I am using SQL Server 2000.
> Please advise..
> Thank you,
> Brett
>

Query the Security Login or Security User of the current person logged in

How do you find out which group membership "Login or User" the current user has permissions to. I currently use Suser_name() for the current username but how do I see what security login or security user he is assosicated to?

I didn't understand what you meant by Security login/Security User.

Do you meant schema name of the current user.. If yes then the following query will help you.

Code Snippet

Select

Loginname = SUSER_SNAME()

, SchemaName = USER_NAME()

|||

Hi,

I think you want this:

Code Snippet

SELECT

G.name As "group_name",

U.name As "user_name"

FROM

sys.database_role_members M

JOIN

sys.sysusers G WITH(NOLOCK)ON M.role_principal_id = G.uid

JOIN

sys.sysusers U WITH(NOLOCK)ON M.member_principal_id = U.uid

WHERE

U.name = @.user_name

Regards,

Janos

|||I ran this as my sysadmin and it through back dbo, but when running as a user I get blanks. Where is it pulling the information from?|||

Hi,

why do not use the system stored procedure sp_helpuser?

Code Snippet

declare @.username sysname

set @.username =user_name()

execsp_helpuser @.username

Query the Security Login or Security User of the current person logged in

How do you find out which group membership "Login or User" the current user has permissions to. I currently use Suser_name() for the current username but how do I see what security login or security user he is assosicated to?

I didn't understand what you meant by Security login/Security User.

Do you meant schema name of the current user.. If yes then the following query will help you.

Code Snippet

Select

Loginname = SUSER_SNAME()

, SchemaName = USER_NAME()

|||

Hi,

I think you want this:

Code Snippet

SELECT

G.name As "group_name",

U.name As "user_name"

FROM

sys.database_role_members M

JOIN

sys.sysusers G WITH(NOLOCK) ON M.role_principal_id = G.uid

JOIN

sys.sysusers U WITH(NOLOCK) ON M.member_principal_id = U.uid

WHERE

U.name = @.user_name

Regards,

Janos

|||I ran this as my sysadmin and it through back dbo, but when running as a user I get blanks. Where is it pulling the information from?|||

Hi,

why do not use the system stored procedure sp_helpuser?

Code Snippet

declare @.username sysname

set @.username = user_name()

exec sp_helpuser @.username