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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 get user's privileges on all objects
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Server_Programmer
Starting Member

Kuwait
7 Posts

Posted - 09/10/2006 :  03:30:50  Show Profile  Reply with Quote
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)

USA
7020 Posts

Posted - 09/10/2006 :  23:11:37  Show Profile  Reply with Quote
This should give you what you want.
exec sp_helprotect


CODO ERGO SUM
Go to Top of Page

Server_Programmer
Starting Member

Kuwait
7 Posts

Posted - 09/11/2006 :  00:36:38  Show Profile  Reply with Quote
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
Go to Top of Page

anilkdanta
Starting Member

India
25 Posts

Posted - 09/15/2006 :  01:58:32  Show Profile  Reply with Quote
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.
Go to Top of Page

Server_Programmer
Starting Member

Kuwait
7 Posts

Posted - 09/17/2006 :  12:49:32  Show Profile  Reply with Quote
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
Go to Top of Page

anilkdanta
Starting Member

India
25 Posts

Posted - 09/19/2006 :  10:06:45  Show Profile  Reply with Quote
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.

Go to Top of Page

Tam_Tam
Starting Member

USA
1 Posts

Posted - 09/16/2009 :  14:59:18  Show Profile  Reply with Quote
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.


Go to Top of Page

Bustaz Kool
Flowing Fount of Yak Knowledge

USA
1834 Posts

Posted - 03/23/2011 :  20:04:15  Show Profile  Reply with Quote
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?)
-----------------------------------------------------------------------------
--- 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


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

Edited by - Bustaz Kool on 03/24/2011 19:29:30
Go to Top of Page

joeller
Starting Member

USA
15 Posts

Posted - 09/22/2011 :  10:55:49  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.04 seconds. Powered By: Snitz Forums 2000