Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Problems accessing SQL Server when NT account has been renamed Expand / Collapse
Author
Message
Posted Thursday, January 31, 2008 3:34 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, March 17, 2014 6:39 AM
Points: 47, Visits: 264
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


Post #449848
Posted Thursday, January 31, 2008 8:05 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, March 6, 2014 2:38 AM
Points: 398, Visits: 2,423
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

Post #450281
Posted Friday, February 1, 2008 6:40 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, February 26, 2009 3:02 PM
Points: 515, Visits: 655
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
Post #450441
Posted Tuesday, February 12, 2008 2:33 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, March 17, 2014 6:39 AM
Points: 47, Visits: 264
Hi Damon, Lester

Sorry for not replying sooner. I will look into your suggestions.

Thanks,

Stephen
Post #454274
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse