May 21, 2010 at 12:22 am
Hi Experts,
How can we find the privileages of each login and access to each database,users and its access to each objects and privileages, roles and its permissions??
Thanks in Advance
May 25, 2010 at 11:13 am
Hi
try
USE yourdb
--get securables permissions assigned
SELECT
CONVERT(NVARCHAR(10), state_desc) COLLATE Latin1_General_CI_AS + ' ' +
CONVERT(NVARCHAR(10), permission_name) COLLATE Latin1_General_CI_AS +
' ON [' + sch.name + '].[' + obj.name + '] TO [' + dbpr.name + ']'
FROM sys.database_permissions dbpe
INNER JOIN sys.database_principals dbpr ON dbpr.principal_id = dbpe.grantee_principal_id
INNER JOIN sys.objects obj ON dbpe.major_id = obj.object_id
INNER JOIN sys.schemas sch ON obj.schema_id = sch.schema_id
where dbpr.name = 'your user'
ORDER BY dbpr.name, obj.name
--Get the database roles the user is a member of too
select pr.name, pr2.name, pr2.type_desc from sys.database_principals pr
inner join sys.database_role_members rm on pr.principal_id = rm.member_principal_id
inner join sys.database_principals pr2 on rm.role_principal_id = pr2.principal_id
where pr.name = 'your user' and pr2.type = 'R'
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
May 25, 2010 at 10:23 pm
Thanks a ton Perry
What if we have 100+ users?
I need to get the same of each and every user in a database.
May 25, 2010 at 10:28 pm
Thanks Perry I modified the script liek this..
SELECT
CONVERT(NVARCHAR(10), state_desc) COLLATE Latin1_General_CI_AS + ' ' +
CONVERT(NVARCHAR(10), permission_name) COLLATE Latin1_General_CI_AS +
' ON [' + sch.name + '].[' + obj.name + '] TO [' + dbpr.name + ']'
FROM sys.database_permissions dbpe
INNER JOIN sys.database_principals dbpr ON dbpr.principal_id = dbpe.grantee_principal_id
INNER JOIN sys.objects obj ON dbpe.major_id = obj.object_id
INNER JOIN sys.schemas sch ON obj.schema_id = sch.schema_id
where dbpr.name in (SELECT name FROM sys.sysusers)
ORDER BY dbpr.name, obj.name
May 26, 2010 at 1:41 am
Ratheesh.K.Nair (5/25/2010)
Thanks a ton PerryWhat if we have 100+ users?
I need to get the same of each and every user in a database.
I would create a database role and put the users into this role and assign any permissions to this role rather than manage that many users
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
May 26, 2010 at 3:43 am
Thanks Perry..
What if we need to give different usrs diffrent privileages??
Can we give different privileges to different users using same role?
May 26, 2010 at 4:19 am
so are you saying that each of the 100 users needs entirely different permissions?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
May 26, 2010 at 4:34 am
not every users but most of the users...
May 26, 2010 at 9:14 am
then group them and create roles for each group of users
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
May 27, 2010 at 7:29 am
Can you please let me know how to group users??
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply