Trying to Drop a Login that owns a Database

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Trying to Drop a Login that owns a Database

Author

Topic

Dallr
Yak Posting Veteran

87 Posts

Posted - 2008-05-01 : 18:59:17

I am trying to drop a login but the system is telling me.

quote:
Msg 15174, Level 16, State 1, Line 3
Login 'Mark' owns one or more database(s). Change the owner of the database(s) before dropping the login.

Question:
But how do i check to find out which objects or tables that this login is associated with.

Dallr

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-01 : 19:04:17

Check sys.databases for which databases the login owns.

You can change the owner via sp_changedbowner. Check BOL for details.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

Dallr
Yak Posting Veteran

87 Posts

Posted - 2008-05-01 : 19:17:06

Thanks for the information Tara. But when i run "SELECT * FROM Sys.Databases" I am not seeing a column where i can pass the login.

I was expecting something like "SELECT * FROM sys.databases WHERE Somefield = 'login_name'; to know which specific databases the login is associated with.

What am i missing?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-01 : 19:26:07

You'll need to use the owner_sid column, join to the sys.server_principals table on sid.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

Dallr
Yak Posting Veteran

87 Posts

Posted - 2008-05-01 : 20:01:42

Thanks Tara this is the final SQL. As you can see I am new to SQL server but I would have thought that there would have been an Stored Procedure to do this instead of writing some SQL.


-- Finding out which login owns a particular database.

SELECT SPS.[NAME] As Login_Name
     , dbs.[Name] As Database_Name
     , dbs.Create_date
FROM sys.server_principals SPS 
   INNER JOIN sys.databases dbs ON sps.SID = dbs.owner_sid 
WHERE sps.[name] = 'Your_login_Name';

Thanks for the help again!

Dallr

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-01 : 20:04:17

I would have thought so too. Perhaps there is and I just didn't find it in BOL.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

Dallr
Yak Posting Veteran

87 Posts

Posted - 2008-05-01 : 20:44:37

Cool, and thanks again

Dallr

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources