get user's privileges on all objects

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

get user's privileges on all objects

Author

Topic

Server_Programmer
Starting Member

7 Posts

Posted - 2006-09-10 : 03:30:50

Hello guys,

Suppose I created a role inside MS-SQL Server, this role name is 'Role_A',

Then I granted
-Select permission on some Tables created on same database
-Execute permission on some Procedures created on same database
-Execute permission on some Functions created on same database

After these steps, I had created also a new user called 'User_A' and added him to the created role 'Role_A'

My question is if the user will inherit all given permission from the Role_A, how can I get a list of privileges of this user.

To be clear,

I knew this instruction "SELECT * FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES"
It is very helpful instruction but unfortunately it is used only to get information about the tables privileges, whereas, I want to get the privileges of this user on all database objects (Tables, Views, Procedures, and Functions)

How to do this?

Thanks a lot

This Forums Helps to Have More Experiance, So Be As Polite Student

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-09-10 : 23:11:37

This should give you what you want.

exec sp_helprotect

CODO ERGO SUM

Server_Programmer
Starting Member

7 Posts

Posted - 2006-09-11 : 00:36:38

Hello Michael, thanks for your caring,
This Instruction gives me the Grantees of the Role only, whereas the user has no private privileges, but only the privileges given to the role.

If I run this stored procedure the result will be like this

Owner, Object, Grantee, Grantor, Protect Type, Action, Column (if available)

Is there any way to add user name to the above result or get the information like below format?

User name, Object, Action

This Forums Helps to Have More Experiance, So Be As Polite Student

anilkdanta
Starting Member

25 Posts

Posted - 2006-09-15 : 01:58:32

Hi,

Run this SQL to see the result on your database.

SELECT su.name AS 'User Name', so.name AS 'Object Name',
Action = CASE sp.action
WHEN 193 THEN 'SELECT'
WHEN 195 THEN 'INSERT'
WHEN 196 THEN 'UPDATE'
WHEN 197 THEN 'DELETE'
WHEN 224 THEN 'EXEC'
ELSE 'UNKNOWN'
END,
Status =
CASE sp.protecttype
WHEN 204 THEN 'GRANT_W_GRANT'
WHEN 205 THEN 'GRANT'
WHEN 206 THEN 'REVOKE'
ELSE 'Unknown'
END

FROM master.dbo.SYSUSERS su, SYSOBJECTS so, SYSPROTECTS sp
WHERE su.uid = so.uid and
so.id = sp.id and
so.xtype IN ('V','P','TR','U') AND LEFT(so.name,3)
NOT IN ('dt_', 'dt', 'sys')

Hope this is what you wanted to see.

Server_Programmer
Starting Member

7 Posts

Posted - 2006-09-17 : 12:49:32

Hello anilkdanta,
thanks a lot for your posted code,

the code results are list with all permissions , for all objects.
this code solved half of my problem, but the user was dbo only,
anyway, thanks for your help.

This Forums Helps to Have More Experiance, So Be As Polite Student

anilkdanta
Starting Member

25 Posts

Posted - 2006-09-19 : 10:06:45

A small change to the previous SQL I have posted here.

FROM SYSUSERS su, SYSOBJECTS so, SYSPROTECTS sp
WHERE su.uid = so.uid and
so.uid = sp.uid

It displays all user names with the objects they own.

Tam_Tam
Starting Member

1 Post

Posted - 2009-09-16 : 14:59:18

I think this may have a small typo. I beleive it should be so.id=sp.id not the uid.

quote:
Originally posted by anilkdanta

A small change to the previous SQL I have posted here.

FROM SYSUSERS su, SYSOBJECTS so, SYSPROTECTS sp
WHERE su.uid = so.uid and
so.uid = sp.uid

It displays all user names with the objects they own.

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2011-03-23 : 20:04:15

I know I'm coming late to the party but your question intrigued me. And besides, what is a year and a half amongst friends? (Add a different version of the product?)[CODE]-----------------------------------------------------------------------------
--- Rights Granted: Given a list of Users in the current database, return
--- the list of effective rights (including Deny) based on the Grants
--- issued to the User, all Roles the User is a member of, membership
--- in fixed database Roles (e.g., db_datareader) and membership in any
--- fixed server roles.
-----------------------------------------------------------------------------

set nocount on

---------------------------------------
--- Define the User(s) of interest
---------------------------------------

declare @AllUsers bit = 0 --<<< SET THIS VALUE
-- 1 = Gather for ALL Users
-- 0 = Gather for explicit list in @Users table
declare @Users table (
UserName sysname
)

insert into @Users (UserName)
values ('MyDomain\MyNTUser') --<<< Populate with a list of Users
-- ,('MySqlUser')

-----------------------------------------------------------------------------

;with AllRoles -- Recursively find all Roles the Users are members of
as (
select principal_id, name, sid, cast(Null as sysname) MemberName, name BaseName
from sys.database_principals
where type in ('S', 'U', 'G')
or name = 'public' -- Public is special since it is not in sys.database_role_members

union all

select r.principal_id, r.name, r.sid, rm.Name, ar.BaseName
from
AllRoles ar
inner join
sys.database_role_members drm
on drm.member_principal_id = ar.principal_id
inner join
sys.database_principals r
on r.principal_id = drm.role_principal_id
inner join
sys.database_principals rm
on rm.principal_id = drm.member_principal_id
)
select a.*
from (
-------------------------------------------
--- Object Level Rights: Explicit Grants
--- (Based on User and Role Memberships)
-------------------------------------------

SELECT
coalesce(so.name, '') AS 'Object Name',

sp.permission_name,
state_desc,

u.Name Grantee,
ar.BaseName
FROM
sys.database_permissions sp -- Rights Granted
inner join
sys.database_principals u -- Grantee
on sp.grantee_principal_id = u.principal_id

left outer join
sys.objects so -- Object
on so.object_id = sp.major_id

inner join
AllRoles ar
on u.sid = ar.sid

WHERE
(
so.name is Null
or
LEFT(so.name,3) NOT IN ('sp_', 'fn_', 'dt_', 'dtp', 'sys')
--AND
--so.type IN ('U','V','TR','P','FN''IF','TF')
)
and not (
sp.class_desc = 'DATABASE'
and sp.permission_name = 'CONNECT'
)
and sp.major_id >= 0 -- Negative => System Object
and (
ar.BaseName = 'public'
or
@AllUsers = 1
or
ar.BaseName in (
select UserName
from @Users
)
)

union all

-------------------------------------------
--- Fixed Database Role Membership
-------------------------------------------

select
ar.Name,
'',
'',
ar.MemberName,
ar.BaseName
from
AllRoles ar
inner join
sys.database_principals r
on r.principal_id = ar.principal_id
and r.is_fixed_role = 1
where
@AllUsers = 1
or
ar.BaseName in (
select UserName
from @Users
)

union all

-------------------------------------------
--- Fixed Server Role Membership
--- (Assumes: Login Name == User Name)
-------------------------------------------

select
sr.Name,
'',
'',
'Server Role',
l.Name
from sys.server_principals l
inner join
sys.server_role_members r
on
r.member_principal_id = l.principal_id
inner join
sys.server_principals sr
on
sr.principal_id = r.role_principal_id
and sr.type = 'R'
where
@AllUsers = 1
or
l.name in (
select UserName
from @Users
)
) a
order by
BaseName,
case
when permission_name = '' then 1 else 2 end,
'Object Name',
permission_name,
state_desc,
Grantee[/CODE]

=======================================
Elitism is the slur directed at merit by mediocrity. -Sydney J. Harris, journalist (1917-1986)

joeller
Starting Member

15 Posts

Posted - 2011-09-22 : 10:55:49

This last shows a list of role membership and explicit permissions but does not show the dervived permission as a result of being a member of a role that users have. As I understood the question that last is what the asker was looking for. (As am I.)

E.R. Joell MCDBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources