Report of user permissions for every database

Question

Report of user permissions for every database

Gonzo

Old Hand

Points: 351
More actions
October 13, 2004 at 2:32 pm

#83860

Does anyone have a sp or query that will create a report of what user permissions exist on all objects in every database on a sql server. This report needs to be given to the security people in spreasheet format for audit purposes...

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

hoo-t SSChampion Points: 12713 More actions · Answer 1

I have one that creates grant/deny statements for reverse engineering the permissions. Because of the way it works, it creates a HUGE result set. Perhaps you could play with it to get the type of results you need. It also requires a modified version of sp_helprotect, which I can include. Let me know if you are interested.

Steve

Gonzo Old Hand Points: 351 More actions · Answer 2

I've seen some that do that (not sure they are yours or not), but they work on one database at a time...I was hoping for something that would loop through all the databases and produce the report for all users on all databases...

esmith21 SSC Journeyman Points: 87 More actions · Answer 3

There is an undocumented stored procedure that does just that called sp_MSforeachdb which lets you execute the same command in every database on the server. Google it or look here for some help on it:

http://www.mssqlcity.com/Articles/Undoc/SQL2000UndocSP.htm

Otherwise you'd have to write a query to get a list of all databases on the server and loop through them.

Nicholas Cain SSC-Dedicated Points: 33009 More actions · Answer 4

Create yourself a proc in the master database that will run the following...

CREATE PROCEDURE SP_PULL_SQL_PERMISSIONS

AS

create table #protectinformation (UID int identity(1,1), Owner VARCHAR(25), object VARCHAR(200), grantee VARCHAR(75), grantor VARCHAR(75), protecttype VARCHAR(10), action varchar(20), [column] varchar(20))

insert into #protectinformation EXEC sp_helprotect NULL, NULL, NULL, 'os'

DECLARE @GRANTTEXT VARCHAR(1000)

declare @IDENT INT, @sql varchar(1000)

select @IDENT=min(UID) from #PROTECTINFORMATION WHERE [UID] > 0 AND OBJECT != '.'

while @IDENT is not null

begin

SELECT @GRANTTEXT = PROTECTTYPE+ ' '+[ACTION]+ ' ON '+OBJECT+ ' TO '+ GRANTEE+ CHAR(13)

FROM #PROTECTINFORMATION WHERE UID = @IDENT

INSERT INTO ADMINDB.DBO.DB_Permissions(SQL_PERMISSIONS) SELECT @GRANTTEXT

select @IDENT=min(UID) from #PROTECTINFORMATION WHERE [UID] > 0 and UID>@IDENT AND OBJECT != '.'

end

drop table #protectinformation

GO

Make sure to create a table in the ADMINDB (or whatever database you want to use) then use the sp_msforeachdb proc to run it against every database. You'll probably want to make changes to this so that it will add the database that you are running against into the table as well so you can further identify the permissions, but it's nice as it will take the permissions for every object and then create a table which you can query directly to return the script to reapply them should they be lost.

exec sp_msforeachdb "use ?; exec sp_pull_sql_permissions;"

Shamless self promotion - read my blog http://sirsql.net

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 5

You can also simply execute the system stored procedure sp_helprotect against each database.

K. Brian Kelley
@kbriankelley

bellis SSCarpal Tunnel Points: 4667 More actions · Answer 6

bellis

SSCarpal Tunnel

Points: 4667

October 14, 2004 at 9:04 am

#526750

exec sp_MSforeachdb @command1 = '?..sp_helprotect'

sa24 SSCrazy Eights Points: 8027 More actions · Answer 7

Don't forget to include members of the fixed database roles i.e. db_datareader, db_datawriter etc.

bellis SSCarpal Tunnel Points: 4667 More actions · Answer 8

Yes, you may want to look at...

sp_helpuser

sp_helplogins

chubbsm SSCommitted Points: 1770 More actions · Answer 9

It seems like sp_helprotect does list "all" permissions on each object in the database. What would the syntax be to list this?

Thanks!

chubbsm SSCommitted Points: 1770 More actions · Answer 10

It seems like sp_helprotect doesn't list "all" permissions on each object in the database. What would the syntax be to list this?

Thanks!

joemai Hall of Fame Points: 3496 More actions · Answer 11

Another one that you can use:

select a.*

FROM information_schema.table_privileges a, information_schema.tables b

where a.table_name = b.table_name

and b.TABLE_TYPE ='base table'

Minh

Gonzo Old Hand Points: 351 More actions · Answer 12

I appreciate all the help so far, but...

What I need is a report or spreadsheet that I can hand over to auditors that shows every permission that every user has on every object in every database.

Does anyone have a comprehensive script/report that would meet these objectives...

I have several servers with many databases on each one, so to keep doing this by cut and paste is a real pain...

Even if there was a third party product that would generate such a report, that would work, I just haven't found one.

Am I really the only one with a security team that is asking for this kind of report?