November 17, 2009 at 1:37 pm
Hi folks
did anyone every come with a valid script fot sql server 2005 ?
Thanks
Jim
November 18, 2009 at 3:59 am
Greetings all
Vivien Xing (7/8/2008)
rbarryyoung (7/8/2008)
Actually, it is not valid to exclude 'dbo' here. If a Login owns a database, then they are user 'dbo' in the database and they own all of the dbo-owned objects.I am trying to find out all the objects owned by a login before I drop it. You are right, this user/login may own a database. If this is the case, need sp_helpdb to find the db owner and transfer db ownership first.
as RBarryYoung has pointed out you probably do not want to exclude 'dbo'
JC-3113 (11/17/2009)
Hi folksdid anyone every come with a valid script fot sql server 2005 ?
Thanks
Jim
this same script below will work for SQL Server 2005!
select name
from sysobjects
where user_name(Uid) <> 'dbo' and user_name(Uid) <> 'sys'
order by name
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
November 18, 2009 at 9:28 am
Hi Perry
does that just give me a llst for the person logged in ?
Thanks
Jim
November 18, 2009 at 11:23 am
JC-3113 (11/18/2009)
Hi Perrydoes that just give me a llst for the person logged in ?
Thanks
Jim
no, not especially. Is that specifically what you are looking for?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
November 23, 2009 at 9:45 am
Hi Perry
what I am looking for is a search of all databases and any objects owned by an account, if any, in these databases.
when i run the script, i get a list like this and I am not sure what I am looking at
it just lools like object types to me:
name
---------------------------
CHECK_CONSTRAINTS
COLUMN_DOMAIN_USAGE
COLUMN_PRIVILEGES
COLUMNS
CONSTRAINT_COLUMN_USAGE
CONSTRAINT_TABLE_USAGE
DOMAIN_CONSTRAINTS
DOMAINS
KEY_COLUMN_USAGE
PARAMETERS
REFERENTIAL_CONSTRAINTS
ROUTINE_COLUMNS
ROUTINES
SCHEMATA
TABLE_CONSTRAINTS
TABLE_PRIVILEGES
TABLES
VIEW_COLUMN_USAGE
VIEW_TABLE_USAGE
VIEWS
Thanks
Jim
November 24, 2009 at 8:03 am
ok, so for any given account on the server you want to ascertain any objects owned in any database, is that correct?
also, you are using SQL Server 2005 (i know you've posted in the SQL2005 forum, but i never take anything for granted 😉 )?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
November 25, 2009 at 9:08 am
Hi Perry
yep. that was my goal. i am on SQL server 2005
Thanks
Jim
November 27, 2009 at 12:26 am
JC-3113 (11/17/2009)
Hi folksdid anyone every come with a valid script fot sql server 2005 ?
Thanks
Jim
The last version that I posted is valid.
Here is a later version, if you want: http://www.sqlservercentral.com/scripts/Administration/63631/.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
December 1, 2009 at 11:06 am
Thanks RBarryYoung
wil look at it
Jim
December 1, 2009 at 3:00 pm
Glad I could help. 🙂
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
December 2, 2009 at 10:30 am
Hi RBarryYoung
i treied to implement and am getting errors on execution:
Msg 102, Level 15, State 1, Server NSAB-SS80-SQL-N, Line 262
Incorrect syntax near '-'.
Msg 102, Level 15, State 1, Server NSAB-SS80-SQL-N, Line 265
Incorrect syntax near '-'.
Msg 102, Level 15, State 1, Server NSAB-SS80-SQL-N, Line 281
Incorrect syntax near '-'.
Msg 102, Level 15, State 1, Server NSAB-SS80-SQL-N, Line 284
Incorrect syntax near '-'.
Msg 102, Level 15, State 1, Server NSAB-SS80-SQL-N, Line 338
Incorrect syntax near '-'.
Msg 102, Level 15, State 1, Server NSAB-SS80-SQL-N, Line 341
Incorrect syntax near '-'.
Msg 102, Level 15, State 1, Server NSAB-SS80-SQL-N, Line 357
Incorrect syntax near '-'.
Msg 102, Level 15, State 1, Server NSAB-SS80-SQL-N, Line 360
Incorrect syntax near '-'.
Msg 102, Level 15, State 1, Server NSAB-SS80-SQL-N, Line 376
Incorrect syntax near '-'.
Msg 102, Level 15, State 1, Server NSAB-SS80-SQL-N, Line 379
Incorrect syntax near '-'.
Msg 102, Level 15, State 1, Server NSAB-SS80-SQL-N, Line 724
Incorrect syntax near ')'.
also can you provide what output should look like as what i am seeing is what looks like the sql i just installed for this procedure for each database onthe server
does this procedure create the sql and then i run the sql that was created for that database ??
guess i am not undetstanding on what i should see
thanks
jim
December 3, 2009 at 12:06 pm
JC-3113 (12/2/2009)
Hi RBarryYoungi treied to implement and am getting errors on execution:
Msg 102, Level 15, State 1, Server NSAB-SS80-SQL-N, Line 262
Incorrect syntax near '-'.
Msg 102, Level 15, State 1, Server NSAB-SS80-SQL-N, Line 265
Incorrect syntax near '-'.
Msg 102, Level 15, State 1, Server NSAB-SS80-SQL-N, Line 281
Incorrect syntax near '-'.
Msg 102, Level 15, State 1, Server NSAB-SS80-SQL-N, Line 284
Incorrect syntax near '-'.
Msg 102, Level 15, State 1, Server NSAB-SS80-SQL-N, Line 338
Incorrect syntax near '-'.
Msg 102, Level 15, State 1, Server NSAB-SS80-SQL-N, Line 341
Incorrect syntax near '-'.
Msg 102, Level 15, State 1, Server NSAB-SS80-SQL-N, Line 357
Incorrect syntax near '-'.
Msg 102, Level 15, State 1, Server NSAB-SS80-SQL-N, Line 360
Incorrect syntax near '-'.
Msg 102, Level 15, State 1, Server NSAB-SS80-SQL-N, Line 376
Incorrect syntax near '-'.
Msg 102, Level 15, State 1, Server NSAB-SS80-SQL-N, Line 379
Incorrect syntax near '-'.
Msg 102, Level 15, State 1, Server NSAB-SS80-SQL-N, Line 724
Incorrect syntax near ')'.
also can you provide what output should look like as what i am seeing is what looks like the sql i just installed for this procedure for each database onthe server
does this procedure create the sql and then i run the sql that was created for that database ??
guess i am not undetstanding on what i should see
thanks
jim
The latest version at this thread should fix this: http://www.sqlservercentral.com/scripts/Administration/63631/%5B/url%5D
If this gives you any problems then post them in that thread also.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
February 1, 2012 at 3:49 pm
Neat script but it has some issues. You can't execute the sql generated once the character string gets over 8000. You can't even print it without a lot of work. I found some work arounds, but it's kind of ungainly on a big box. I think you'd be better off building a table to capture the results of the query and just iterate through the databases via cursor or loop using sysdatabases.
Here is a similar solution (borrowing some of your code) that doesn't require working around the 8000 character limit.
CREATE TABLE #ObjectOwnership (
[DBID] INT
, DBName VARCHAR(255)
, [LOGIN] VARCHAR(255)
, [User] VARCHAR(255)
, [name] VARCHAR(255)
, [object_id] INT
, [principal_id] INT
, [schema_id] INT
, [parent_object_id] INT
, [type] VARCHAR(10)
, [type_desc] VARCHAR(255)
, [create_date] DATETIME
, [modify_date] DATETIME
, [is_ms_shipped] BIT
, [is_published] BIT
, [is_schema_published] BIT)
DECLARE
@dbID VARCHAR(9)
, @dbName sysname
, @dbException VARCHAR(1000)
, @login VARCHAR(255)
, @sql VARCHAR(4000)
SET @dbException = NULL --> could be 'master, tempdb' etc.
SET @login = 'sa'
DECLARE csr CURSOR FOR
SELECT DISTINCT CONVERT(VARCHAR(9), database_id), [name]
FROM sys.databases
WHERE @dbException IS NULL
OR [name] NOT IN (@dbException)
OPEN csr
FETCH csr INTO @dbID, @dbName
WHILE @@fetch_status >= 0
BEGIN
SET @sql =
'Insert #ObjectOwnership
Select ' + @dbID + ' as DBID, ''' + @dbName + ''' as DBName, L.name as Login, U.Name as [User], O.*
From [' + @dbName + '].sys.objects o
Join [' + @dbName + '].sys.database_principals u
ON Coalesce(o.principal_id, (Select S.Principal_ID from [' + @dbName + '].sys.schemas S Where S.Schema_ID = O.schema_id)) = U.principal_id
left join [' + @dbName + '].sys.server_principals L
on L.sid = u.sid
Where L.name = ''' + @login + ''''
PRINT @sql
EXEC(@sql)
FETCH csr INTO @dbID, @dbName
END
CLOSE csr
DEALLOCATE csr
SELECT *
FROM #ObjectOwnership
February 24, 2012 at 1:03 pm
sgtmango333 (2/1/2012)
You can't execute the sql generated once the character string gets over 8000.
Absolutely NOT true. You can execute huge variables well beyond 8k using VARCHAR(MAX) and NVARCHAR(MAX) even if you can't easily display the contents of such large variables.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 24, 2012 at 1:08 pm
Well I tried and couldn't get it to work. So if you know the secret, please share. I spent half a day trying and it kept failing.
Viewing 15 posts - 16 through 30 (of 39 total)
You must be logged in to reply to this topic. Login to reply