SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Problems accessing SQL Server when NT account has been renamed


Problems accessing SQL Server when NT account has been renamed

Author
Message
SJB42
SJB42
Valued Member
Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)

Group: General Forum Members
Points: 61 Visits: 285
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
Lester Policarpio
Lester Policarpio
Say Hey Kid
Say Hey Kid (663 reputation)Say Hey Kid (663 reputation)Say Hey Kid (663 reputation)Say Hey Kid (663 reputation)Say Hey Kid (663 reputation)Say Hey Kid (663 reputation)Say Hey Kid (663 reputation)Say Hey Kid (663 reputation)

Group: General Forum Members
Points: 663 Visits: 2459
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
Key DBA
Key DBA
SSC Eights!
SSC Eights! (831 reputation)SSC Eights! (831 reputation)SSC Eights! (831 reputation)SSC Eights! (831 reputation)SSC Eights! (831 reputation)SSC Eights! (831 reputation)SSC Eights! (831 reputation)SSC Eights! (831 reputation)

Group: General Forum Members
Points: 831 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
SJB42
SJB42
Valued Member
Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)

Group: General Forum Members
Points: 61 Visits: 285
Hi Damon, Lester

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

Thanks,

Stephen
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search