Determining Object Owner...
-
I have an issue that I need to resolve.
We recently had one of our SQL Server DBAs leave the company and now I am tasked with trying to locate any objects that he may have created (databases, tables, stored procedures, etc.) during his time here.
I need to write a query that I can run on each of our servers (close to 100 mostly SQL Server 2000 with a few SQL Server 2005) that goes through all the databases and returns any object owned by any form of his login (ex: jon, john, johnny, jonathan).
The long term goal is once all the objects have been identified would be to change ownership to the sa account.
I am a fairly new DBA so please bear with me.
I can get the list of logins from the Master database via the sysxlogins table.
I also see each database has a sysobjects table but I do not see any specific object names and any correlation to the sysxlogins table.
Any help would be greatly appreciated!
Thanks.
-
Here's how we do it.
1. Pick a server that will be your 'reporting' server.
2. Created linked servers from the reporting server to all the other servers.
3. Create a table called tServer with ServerID and ServerName columns at a minimum.
4. Create a table called tLogin with ServerName and Login columns.
5. Create this UDF.
CREATE FUNCTION dbo.udfGetServer(@id AS INT)
RETURNS VARCHAR(20)
AS
BEGIN
DECLARE @value VARCHAR(20)
SET @value = ''
SELECT @value = @value + CONVERT(VARCHAR(20), ServerName)
FROM dbo.tServer
WHERE ServerId = @id
ORDER BY ServerName
Return @value
END
6. Create this stored procedure.
CREATE PROCEDURE dbo.spGetPersonsObjects
@personlogin VARCHAR(10)
AS
/*
File Name: spGetPersonsObjects.sql
Created By: William Fleming
Created: 3 November 2006
Purpose: This script will return all objects owned by the
login entered as a variable.
*/
BEGIN
DECLARE @ServerID CHAR(5),
@login VARCHAR(50),
@ServerName VARCHAR(30),
@ServerCount INT,
@ServerPosition INT,
@FullName VARCHAR(30),
@FullName1 VARCHAR(30),
@FullName2 VARCHAR(300),
@string VARCHAR(2000)
SELECT @ServerCount=MAX(ServerID) + 1
FROM tServer
SELECT @ServerPosition = 0
BEGIN
WHILE @ServerPosition < @ServerCount
BEGIN
SELECT @FullName=dbo.udfGetServer (@ServerPosition)
IF @FullName = ''
WHILE @FullName = ''
BEGIN
SET @ServerPosition=@ServerPosition +1
SELECT @FullName=dbo.udfGetServer (@ServerPosition)
END
SELECT @FullName1 = @FullName
SET @FullName2 = '['+ rtrim(@FullName1) + '].master.dbo.syslogins j'
SET @serverID = @serverposition
SET @string = 'INSERT tLogins ' + 'SELECT '''+ @FullName + ''', j.name
FROM '+ @FullName2 + ' ORDER BY j.name'
--PRINT @string
EXEC (@string)
SET @ServerPosition=@ServerPosition +1
End
End
END
GO
7. Add your server information to tServer.
8. Run the stored procedure. If you want to test it, comment out the EXEC(@string) line and uncomment the PRINT @string line.
-SQLBill
-
Hi John,
This is a slightly confusing area with in SQL Server (but for a good reason) - Users and Logins are different things.
A login may be a Windows User with access rights granted in the SQL Server or it may be a SQL Server login such as sa. These are detailed in the syslogins table.
A user is database specific and a login can be mapped to it within a database. It is used to control access and ownership of objects within a database and independently of a login. (Two or more logins may map to the same user).
A user owns a database object rather than a login. This is normally the dbo user. http://www.sqlservercentral.com/columnists/nboyle/fixingbrokenlogins.asp details where the information for mapping logins to users in a database is stored.
So you can find all objects owner by a user within a database using sysobjects and sysusers but Im not sure finding all of the objects created by a login regardless of the user within a database is possible.. it might be though
Hope this gets you on your way,
- James
--
James Moore
Red Gate Software Ltd -
Thanks for the responses gentlemen!
I will put this knowledge into use tomorrow and see how I make out.
Thank you both for taking the time to help me out- it is much appreciated!!!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply