Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

List all permission in all databases Expand / Collapse
Author
Message
Posted Sunday, March 15, 2009 6:55 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, October 16, 2014 3:03 PM
Points: 1,109, Visits: 556
Comments posted to this topic are about the item List all permission in all databases

Alejandro Pelc
Post #676084
Posted Thursday, April 16, 2009 9:19 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 9:00 AM
Points: 162, Visits: 1,044
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



Post #698579
Posted Thursday, April 16, 2009 1:12 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, January 12, 2011 8:28 AM
Points: 162, Visits: 180
The script is only for SQL 2005...
Post #698808
Posted Thursday, April 16, 2009 2:48 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 8:10 AM
Points: 323, Visits: 1,466
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
Post #698914
Posted Thursday, April 30, 2009 5:25 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, December 10, 2009 1:50 AM
Points: 19, Visits: 132
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
Post #707577
Posted Thursday, April 30, 2009 5:37 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, October 16, 2014 3:03 PM
Points: 1,109, Visits: 556
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
Post #707583
Posted Friday, October 2, 2009 4:52 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, September 2, 2014 3:06 AM
Points: 3, Visits: 20
Thx for a nice script without errors Keep up the good work, mate!
Post #796814
Posted Thursday, December 10, 2009 11:31 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 7, 2010 3:34 AM
Points: 1, Visits: 5
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
Post #832735
Posted Wednesday, May 18, 2011 9:21 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, September 19, 2014 12:25 PM
Points: 23, Visits: 97
Very nice, thanks. I forwarded to my team.

Tom Powell
http://philergia.wordpress.com/
Post #1111091
Posted Wednesday, May 18, 2011 9:24 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, September 8, 2014 8:18 AM
Points: 26, Visits: 265
Katrina, these variables were meant to be temporary.
Post #1111093
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse