Script to find all objects owned by a user (loginname)

Question

Script to find all objects owned by a user (loginname)

Viewing 10 posts - 31 through 39 (of 39 total)

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

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 1

sgtmango333 (2/24/2012)
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.

Were you using a target variable of VARCHAR(MAX)???

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 2

sgtmango333 (2/24/2012)
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.

Here's simple proof that you can execute dynamic SQL that's greater than 8,000 characters...

SET NOCOUNT ON;

DECLARE @SQL VARCHAR(MAX)

;

WITH

cteTally AS

( --=== Build numbers from 1 to 10,000

SELECT TOP 10000

N = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))

FROM sys.all_columns ac1

CROSS JOIN sys.all_columns ac2

) --=== Build 10,000 SELECT statements and put them in a single variable.

-- Obviously, this will build @SQL out to be more than 8,000 characters

SELECT @SQL = ISNULL(@SQL,'') + 'SELECT ' + CAST(N AS VARCHAR(10)) + ';'

FROM cteTally

;

--===== Show just how many characters are in the variable to be executed.

SELECT LEN(@SQL) --This turns out to be 118,894 characters... just a wee bit bigger than 8k

;

--===== This executes the "large" SQL.

EXEC (@SQL)

;

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

sgtmango333 Old Hand Points: 336 More actions · Answer 3

I used the script provided earlier in this thread. Yes, it was a varchar(max) and the resulting query built out to somewhere between 12k and 13k characters.

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 4

sgtmango333 (2/24/2012)
I used the script provided earlier in this thread. Yes, it was a varchar(max) and the resulting query built out to somewhere between 12k and 13k characters.

So, why do you think it didn't work?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

sgtmango333 Old Hand Points: 336 More actions · Answer 5

sgtmango333

Old Hand

Points: 336

February 24, 2012 at 4:57 pm

#1451644

Ummmm...the failure messages.

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 6

sgtmango333 (2/24/2012)
Ummmm...the failure messages.

BWAAA-HAAA!!! Please post the actual code you're using, the failure messages, and anything else that you might be able to provide to help us help you. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Lowell SSC Guru Points: 323518 More actions · Answer 7

sgtmango333 (2/24/2012)
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.

If you are printing the command to SSMS, and then trying to execute (copying the results to the query window) of what was printed, that is an issue with SSMS.

SSMS will only display the first 8000 chars of a varchar(max). it's a compromise for perforamance.

insert the string into a variable and execute it, or print to file.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

JManuelN SSCommitted Points: 1708 More actions · Answer 8

Hi... I have tried to save the char limit and collation issues using the msforeach_db stored procedure. I hope this helps.

I don not if I am using the latest query version, please review it.

DECLARE @login sysname

DECLARE @sqlcommand varchar(max)

SET @login = 'sa'

SET @sqlcommand = 'SELECT DB_ID(''?'') AS [DBID], ''?'' as DBName, l.name as [Login], u.Name as [User], u.Name COLLATE Latin1_General_CS_AS_KS_WS AS [User] , o.[name] COLLATE Latin1_General_CS_AS_KS_WS AS [object_name], o.[object_id], o.[principal_id], o.[schema_id], o.[parent_object_id], o.[type], o.[type_desc], o.[create_date], o.[modify_date], o.[is_ms_shipped], o.[is_published], o.[is_schema_published] FROM sys.objects o JOIN sys.database_principals u ON COALESCE(o.principal_id, (SELECT s.Principal_ID FROM sys.schemas s WHERE s.Schema_ID = o.schema_id)) = u.principal_id LEFT JOIN sys.server_principals l ON l.sid = u.sid WHERE l.name = ' + QUOTENAME(@Login,'''')

--PRINT @sqlcommand

EXECUTE sp_MSforeachdb @sqlcommand

JRoughgarden Ten Centuries Points: 1119 More actions · Answer 9

R Barry Young's procedure fails if any databases have conflicting collations. Report Server databases have Latin1_General_CI_AS_KS_WS collations, whereas the default collation is often SQL_Latin1_General_CP1_CI_AS.

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 10

JRoughgarden (2/19/2016)
R Barry Young's procedure fails if any databases have conflicting collations. Report Server databases have Latin1_General_CI_AS_KS_WS collations, whereas the default collation is often SQL_Latin1_General_CP1_CI_AS.

OK, so can you post the code that eventually worked for you so the next "you" can pickup a working solution?

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato