List Groups Along with Tables and Permissions 3

[kjv1611]

How can I write a query that will show something like:

[TT]
UserName(OrGroup) TableName Permission
lbrown Table1 SELECT
jbodine Table1 SELECT
DbUpdaters Table1 SELECT
DbUpdaters Table1 UPDATE
lbrown Table2 SELECT
jbodine Table2 SELECT
lbrown Table2 UPDATE
jbodine Table2 UPDATE
DbUpdaters Table2 SELECT
DbUpdaters Table2 UPDATE
[/TT]

I've gotten the following from a posting on mssqltips.com, and trying to edit for my current purpose. So I've gotten this which gives me sort of a framework of close to what I'm looking for, but still not really..

SELECT SDP.state_desc, SDP.permission_name, SSU.[name] AS [Schema] ,SSO.[name], SSO.[type]
FROM		MyDatabase.sys.sysobjects SSO 
LEFT JOIN MyDatabase.sys.database_permissions SDP ON SSO.id = SDP.major_id 
LEFT JOIN MyDatabase.sys.sysusers SSU ON SSO.uid = SSU.uid
WHERE	SSO.type IN ('V','U')
ORDER BY SSU.[name], SSO.[name]

What I'm primarily after is to see what groups (listed with Users) have permissions to what tables/views/objects).

Thanks for any information,



"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[SQLScholar]

Hi,

When you say "groups (listed with Users)" are these groups that are defined within Active Directory?

If so the user list isnt in SQL its in Active Directory. It is possible to query AD via SQL (ADSI query). Just google it!

Dan

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

http://www.fountain.me.uk

Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Bernard Baruch

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------

 

[kjv1611]

Well, the groups I'm looking for do show in the query I pasted, but I'm not getting the final details I'm looking for. The SQL Table permissions wouldn't be in AD, would they? I assumed they'd all be in SQL Server.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[djj55]

See if this helps

DECLARE @DBuser_sql VARCHAR(4000)
DECLARE @DBuser_table TABLE (DBName VARCHAR(200), UserName VARCHAR(250), LoginType VARCHAR(500), AssociatedRole VARCHAR(200))
SET @DBuser_sql='SELECT ''?'' AS DBName,a.name AS Name,a.type_desc AS LoginType,' 
+'USER_NAME(b.role_principal_id) AS AssociatedRole FROM ?.sys.database_principals a
LEFT OUTER JOIN ?.sys.database_role_members b ON a.principal_id=b.member_principal_id
WHERE a.sid NOT IN (0x01,0x00) AND a.sid IS NOT NULL AND a.type NOT IN (''C'') AND a.is_fixed_role <> 1 '
+'AND a.name NOT LIKE ''##%'' AND ''?'' NOT IN (''master'',''msdb'',''model'',''tempdb'') ORDER BY Name'
INSERT @DBuser_table
EXEC sp_MSforeachdb @command1=@dbuser_sql
SELECT * FROM @DBuser_table ORDER BY username

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[kjv1611]

Thanks... Running your query, I am able to see that they are indeed Windows_Group login types. So, if that's the case, I suppose I'm back to going via the ADSI query mentioned by SQLScholar to find the actual permissions? I would have thought I could see things like... UserNAME GROUP1 Has Select, Update permissions on SQLTable1, etc.. and see that directly within SQL..

Well, I'll see what I can find later on ADSI. It wasn't something I HAVE to do, but got really curious when someone asked me a question the other day.



"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[dhulbert]

Have a look at SYS.database_permission this lists all of the permissions assigned within the current database, there is also SYS.syspermissions which lists all of the permissions for the server.

Major_id links back to the id in sys.sysobjects, Grantee and Grantor principle_id's link back to the uid in sys.sysusesrs.

So something like this should list any permissions assigned to any object in the database and the users they are assigned to .

SELECT	*
FROM	sys.database_permissions dbp
		left outer join
		sys.sysobjects sob
		on
		dbp.major_id = sob.id
		left outer join
		sys.sysusers su
		on
		dbp.grantee_principal_id = su.uid

I love deadlines. I like the whooshing sound they make as they fly by
Douglas Adams
(1952-2001)

 

[dhulbert]

Should have said that this works for SQL server 2008R2.

Not sure about older versions.

I love deadlines. I like the whooshing sound they make as they fly by
Douglas Adams
(1952-2001)

 

[SQLScholar]

Just to clarify.

To find who is in what group - that is managed in active director and can be accessed via ADSI.

To find out what group has what permissions in SQL - thats in SQL. So you need to look at bringing the two streams of data together.

Dan

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

http://www.fountain.me.uk

Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Bernard Baruch

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------

 

[kjv1611]

Thanks for the query, dhulbert. I think that's really what I'm looking for. Thanks a ton!

Thanks for the additional note, SQLScholar, as well.
It'd be interesting to find who is in what groups, but at the moment, I am mainly interested in what permissions were assigned to what user/group.



"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57