List all permission in all databases

Question

List all permission in all databases

Ale Pelc

SSCrazy

Points: 2421
More actions
March 15, 2009 at 6:55 am

#207005

Comments posted to this topic are about the item List all permission in all databases
Alejandro Pelc

Viewing 15 posts - 1 through 15 (of 17 total)

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

hmbtx SSCarpal Tunnel Points: 4664 More actions · Answer 1

I get the following sql error message when running the script in SQL Server 2000 Query Analyzer or 2005 SQL Server Management Studio Express.

Server: Msg 197, Level 15, State 1, Line 64

EXECUTE cannot be used as a source when inserting into a table variable.

Thanks,

Howard

Glynne Smith SSC Eights! Points: 804 More actions · Answer 2

Glynne Smith

SSC Eights!

Points: 804

April 16, 2009 at 1:12 pm

#979153

The script is only for SQL 2005...

ken.trock SSCertifiable Points: 5147 More actions · Answer 3

I made a slight change in mine to take into account an Offline DB:

insert into @dbs

select name from sys.databases where state_desc 'offline' order by name

Ken

J-983369 Mr or Mrs. 500 Points: 535 More actions · Answer 4

Hi,

Sorry I'm new to sql server 2005 and coding is not my strongest point. Can you please point me to exactly which line that I will need to change if I want to run this script for a specific db?

Thanks

J

Ale Pelc SSCrazy Points: 2421 More actions · Answer 5

Hi there,

the simplest way is replacing this line

insert into @dbs

select name from sys.databases order by name

with this one

insert into @dbs

values ('Name_of_DB')

The nicest way will be removing the whole loop, but if you want to all DBs in the future, then you should add the loop again

cheers

Alejandro Pelc

jensbjpersson SSC Rookie Points: 37 More actions · Answer 6

Thx for a nice script without errors 🙂 Keep up the good work, mate!

cutiekatrinakaif SSC Rookie Points: 39 More actions · Answer 7

Hi Everyone,

I am new to scripting and I want to fetch the security information on a wide set of servers and databases including Production environment. Could you please confirm if this script creates the three variables permanently or are they meant to be temporary variables?

Many thanks in advance

Katrina

Tom Powell-334692 Old Hand Points: 366 More actions · Answer 8

Very nice, thanks. I forwarded to my team.

[font="Comic Sans MS"]Tom Powell
http://philergia.wordpress.com/[/font]

deanroush SSC-Addicted Points: 474 More actions · Answer 9

Katrina, these variables were meant to be temporary.

chumphrey 12211 Ten Centuries Points: 1223 More actions · Answer 10

Also must be SP2 or higher. That is when OBJECT_SCHEMA_NAME was introduced.

Preet_S SSCrazy Points: 2184 More actions · Answer 11

Hi Alejandro

What is the purpose of

[font="Courier New"]select id, xtype from master.sys.sysobjects[/font]

is sys.objects not sufficient ?

Also would it better to change the join order i.e. if we are after permissions should we be using database_permissions as the base table.This will prevent nulls being returned where database roles have no explicitly defined permissions ?

We could just use [font="Courier New"]where Permission is not null [/font] I guess.

Lastly how can we get schema names to appear under the object name column ? I am seeing the following permissions ;

ALTER

CONTROL

DELETE

EXECUTE

INSERT

REFERENCES

SELECT

TAKE OWNERSHIP

UPDATE

VIEW CHANGE TRACKING

VIEW DEFINITION

with no object name. Clearly it applies to a schema but it would be useful to get the schema name.

Cheers

Preet

terry.home Ten Centuries Points: 1004 More actions · Answer 12

Would it be possible to amend this to also work on 2008 R2?

Also, seem to get an error when it reaches an offline database (so could be modified to ignore offline databases)

Terry

timwell SSCertifiable Points: 5315 More actions · Answer 13

terry.home (11/18/2013)
Would it be possible to amend this to also work on 2008 R2?
Also, seem to get an error when it reaches an offline database (so could be modified to ignore offline databases)

I had the same issue (using 2008 R2). See the post by ken.trock earlier in the discussion for the solution.

rw_ebox SSC Eights! Points: 802 More actions · Answer 14

What a surpprise! Your script even support those funny long name of Sharepoint database.

Execellent job!

Thanks,

Ray Wang