A SQL script to run through logins to list permissions and roles?

[jlrsov]

To all SQL Server gurus, please help!

Does anyone has a SQL script that can start at the beginning of the logins (in the Security section for that server) and list, for each login, their respective server roles and database permissions? What about checking or scanning through a whole group of servers?

I have about 1500 SQL Servers to check through.

Thanks,
Jim Ruddy

 

[katgirl]

here ya go:

sp_helplogins

 

[katgirl]

oops, that's not it , try this (2005 version)

CODE
select sys.schemas.name 'Schema', sys.objects.name Object, sys.database_principals.name username, sys.database_permissions.type permissions_type,
sys.database_permissions.permission_name,
sys.database_permissions.state permission_state,
sys.database_permissions.state_desc
from sys.database_permissions
join sys.objects on sys.database_permissions.major_id =
sys.objects.object_id
join sys.schemas on sys.objects.schema_id = sys.schemas.schema_id
join sys.database_principals on sys.database_permissions.grantee_principal_id =
sys.database_principals.principal_id
order by 1, 2, 3, 5

 

[mrdenny]

Gee... That looks familer.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[katgirl]

yes ! you don't mind me sharing do you?

 

[jlrsov]

I have about 1500 SQL Server 2000 servers to scan, and this script doesn't work with SQL Server 2000. Do you have an older version that would do the trick?

Thanks,
Jim

 

[mrdenny]

No, I don't mind at all. Just giving you a hard time.

Here is a script that works on SQL 2000.

select	
	user_name(p.grantor)	as GRANTOR
	,user_name(p.uid)		as GRANTEE
	,db_name()				as TABLE_CATALOG
	,user_name(o.uid)		as TABLE_SCHEMA
	,o.name					as TABLE_NAME
	,case p.action		
		when 26  then 'REFERENCES'
		when 193 then 'SELECT'
		when 195 then 'INSERT'
		when 196 then 'DELETE'
		when 197 then 'UPDATE'
		when 224 then 'EXECUTE'
	end						as PRIVILEGE_TYPE
	,case 
		when p.protecttype = 205 then 'NO'
		else 'YES'
	end						as IS_GRANTABLE
 from 
	sysprotects p, 
	sysobjects o
where  
	 (p.protecttype = 204 or 	/*grant exists without same grant with grant */
	(p.protecttype = 205
		and not exists(select * from sysprotects p2
				where p2.id = p.id and
				p2.uid = p.uid and 
				p2.action = p.action and 
				p2.columns = p.columns and
				p2.grantor = p.grantor and
				p2.protecttype = 204)))
 	and p.action in (26,193,195,196,197,224)
 	and p.id = o.id
	and o.xtype in ('U', 'V', 'P')
 	and 0 != (permissions(o.id) &
		case p.action
			when 26  then 	4		/*REFERENCES basebit on all columns	*/		
			when 193 then 	1		/*SELECT basebit on all columns	*/		
			when 195 then 	8		/*INSERT basebit */
			when 196 then 	16		/*DELETE basebit */
			when 197 then 	2		/*UPDATE basebit on all columns	*/
			when 224 then	32		/*Execute on procedures*/
		end)
order by PRIVILEGE_TYPE

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[jlrsov]

dear mrkenny

I ran the above script for 2000 on one of the servers that had 10 logins created in the Security folder and all that came up was 648 rows of "dbo" and "INFORMATION_SCHEMA" as Grantor...
Am I missing something? Where are the ten logins with their database permissions listed?

Jim

 

[mrdenny]

The specific users will be in the Grantee column. If the users are getting there rights via a role the users them selves won't show up. Only the roles will (unless they are microsoft specific roles).

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com