List all permission in all databases

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

    Alejandro Pelc

  • 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

  • The script is only for SQL 2005...

  • 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

  • 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

  • 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

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

  • 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

  • Very nice, thanks. I forwarded to my team.

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

  • Katrina, these variables were meant to be temporary.

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

  • 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

  • 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

  • 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.

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

    Execellent job!

    Thanks,

    Ray Wang

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

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