List all permission in all databases

  • Ale Pelc

    SSCrazy

    Points: 2421

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

    Alejandro Pelc

  • hmbtx

    SSCarpal Tunnel

    Points: 4664

    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

    The script is only for SQL 2005...

  • ken.trock

    SSCertifiable

    Points: 5147

    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

    SSC-Addicted

    Points: 475

    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

    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

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

  • cutiekatrinakaif

    SSC Rookie

    Points: 39

    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

    Very nice, thanks. I forwarded to my team.

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

  • deanroush

    SSC-Addicted

    Points: 474

    Katrina, these variables were meant to be temporary.

  • chumphrey 12211

    Ten Centuries

    Points: 1223

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

  • Preet_S

    SSCrazy

    Points: 2124

    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

    SSC Eights!

    Points: 864

    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: 5102

    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

    Say Hey Kid

    Points: 690

    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 18 total)

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