Query Privileges

Hi NG,
How do I select the privileges for a particular user in SQL Server?
sp_helpuser doesn't help. I need to get the list of tables, views etc. that
a particular user have access.
****************************************
********
This is how I do it in Oracle.
select * from DBA_TAB_PRIVS where grantee = username
****************************************
********
Thank you in advance.Hi
I think Aaron wrote this script
CREATE FUNCTION dbo.RoleCheckUser
(
@.UserName sysname,
@.RoleName sysname
)
RETURNS BIT
AS
BEGIN
DECLARE @.RetVal BIT
SET @.RetVal = 0
SELECT @.RetVal = 1
WHERE EXISTS
(
SELECT *
FROM sysmembers membs
JOIN sysusers users on membs.memberuid = users.uid
JOIN sysusers groups on membs.groupuid = groups.uid
WHERE
users.name = @.UserName
AND groups.name = @.RoleName
)
RETURN @.RetVal
END
GO
-- Syntax to use the created function
SELECT dbo.RoleCheckUser('dbo', 'db_owner')
GO
"Praetorian Guard" <praetorian@.gatekeeper.com> wrote in message
news:OcAvzDeJIHA.3400@.TK2MSFTNGP03.phx.gbl...
> Hi NG,
> How do I select the privileges for a particular user in SQL Server?
> sp_helpuser doesn't help. I need to get the list of tables, views etc.
> that a particular user have access.
> ****************************************
********
> This is how I do it in Oracle.
> select * from DBA_TAB_PRIVS where grantee = username
> ****************************************
********
> Thank you in advance.
>|||Try executing EXEC sp_helprotect under the required database.
Manu
"Praetorian Guard" wrote:

> Hi NG,
> How do I select the privileges for a particular user in SQL Server?
> sp_helpuser doesn't help. I need to get the list of tables, views etc. tha
t
> a particular user have access.
> ****************************************
********
> This is how I do it in Oracle.
> select * from DBA_TAB_PRIVS where grantee = username
> ****************************************
********
> Thank you in advance.
>
>|||Praetorian Guard (praetorian@.gatekeeper.com) writes:
> How do I select the privileges for a particular user in SQL Server?
> sp_helpuser doesn't help. I need to get the list of tables, views etc.
> that a particular user have access.
> ****************************************
********
> This is how I do it in Oracle.
> select * from DBA_TAB_PRIVS where grantee = username
> ****************************************
********
If you are on SQL 2005, have a look on fn_my_permissions and
Has_Perms_By_Name. fn_my_permissons would have been really useful,
had it only accepted a column for the first parameter, but it appears
to only accept strings and variables, so you would have to run a cursor
over it. Has_Perms_By_Name takes a column so it can be used a query,
but you can only check one permission at time.
Both presume that you impersonate the user in question with EXECUTE AS.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx