January 31, 2008 at 3:34 am
Hi Everybody,
I wonder if anybody can offer any advice on the following.
We use NT authentication to connect to SQL Server 2000. Recently, a number of our developers had their NT domain accounts renamed. However, the old logins were not removed from SQL Server. The problem is now this - when we try to remove the old logins from SQL Server it says that the logins can't be dropped because they own objects. We reviewed the databases and on the face of it they don't appear to own any objects i.e. user..objectname. All objects are owned by dbo. However, the developers in question were members of the db_owenrs role (from which they've not been removed) so I'm assuming that any objects they created behind the scenes they will own - my assumption, of course may be completely wrong!!!
When we try to add the developers new logins to SQL Server, SQL says that the NT account does not exist. I'm assuming the problem here is that because the old NT login is still in the syslogins table SQL is having a problem adding the renamed login because it has the same SID. Again, this maybe completely wrong.
Does anybody have any advice or have experienced a similar situation?
Cheers,
Stephen
January 31, 2008 at 8:05 pm
when you say object name did you check not only tables but also views and store procedures? Dont know if this will help but you can try it anyway
--CREATE TABLE 'user_owner(lester)'
IF NOT EXISTS (select name from sysobjects where xtype = 'U' and name = 'user_owner_lester')
CREATE TABLE user_owner_lester
(Name varchar(50), uid int, DB_Name varchar(80),UserName varchar(50),Object_Type varchar(50))
INSERT INTO user_owner_lester exec sp_msforeachdb
'use ?
select sysobjects.name as ''Object Name'' , sysobjects.uid as ''User ID'',db_name() as ''Database Name'',
sysusers.name as ''User Name'',
CASE sysobjects.xtype
WHEN ''V'' THEN ''Views''
WHEN ''U'' THEN ''Tables''
WHEN ''P'' THEN ''Stored Procedures''
END as ''Object Type''
from sysobjects,sysusers where sysobjects.uid = sysusers.uid
and sysobjects. xtype in (''U'',''V'',''P'') and
sysobjects.uid in (select uid from sysusers where name not like ''db_%'' and name not in (''guest'',''dbo'',''public''))
order by db_name(),sysobjects.xtype
'
Select Name as 'Object Name', uid as 'User ID', DB_Name 'Database Name',
UserName as 'User Name', Object_Type as 'Object Type'
From user_owner_lester order by db_name,object_type
DROP TABLE user_owner_lester
"-=Still Learning=-"
Lester Policarpio
February 1, 2008 at 6:40 am
Stephen,
The script below can also be modified to suit your needs. It should crawl all database objects and report on ownership for you. Thanks to Scott Coleman for being the original poster of the code with SQL 2000 and SQL 2005 syntax.
-- Author: Scott Coleman
-- Date: 15-JAN-2008
--Source: http://www.sqlservercentral.com/Forums/Topic413580-359-1.aspx
--
-- SQL 2000
-- In SQL 2000 the owner or schema of an object was indicated by the
-- uid value in sysobjects and could be looked up in sysusers.
SELECT o.xtype, QUOTENAME(u.name) + '.' + QUOTENAME(o.name) AS Object, u.name AS Owner
FROM sysobjects AS o
INNER JOIN sysusers AS u ON u.uid = o.uid
WHERE ( u.name = 'dbo' )
-- SQL 2005
-- In SQL 2005 the owner of an object may be indicated by principal_id (links
-- to sys.database_principals), but if this is null then ownership defaults
-- to the schema_id (links to sys.schemas).
SELECT o.type_desc, QUOTENAME(s.name) + '.' + QUOTENAME(o.name) AS Object,
COALESCE(p.name, s.name) AS OwnerName
FROM sys.all_objects AS o
INNER JOIN sys.schemas AS s ON s.schema_id = o.schema_id
LEFT JOIN sys.database_principals AS p ON p.principal_id = o.principal_id
WHERE COALESCE(p.name, s.name) = 'dbo'
Once you have been able to clean up the owner/name change issues, you may want to consider adopting a change to your object creation syntax. If you use a "dbo." preface for all object creation, it won't matter who creates the object. This method may not be ideal in Development or Test environments, but would be a lot cleaner when promoting code to Production.
Example:
-- creates a table owned by the user I am logged in as
CREATE TABLE MyTable (MyNumber int, MyString varchar(50))
-- creates a table owned by "dbo"
CREATE TABLE dbo.AnotherTable (AnotherNumber int, AnotherString varchar(50))
Hope This Helps
"Key"
MCITP: DBA, MCSE, MCTS: SQL 2005, OCP
February 12, 2008 at 2:33 am
Hi Damon, Lester
Sorry for not replying sooner. I will look into your suggestions.
Thanks,
Stephen
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply