suser_sid returns value for nonexistent login

postwick

I have code that runs suser_sid and if it is null it creates a login, then uses sp_adduser to add that login to the database. If suser_sid is not null then it just runs sp_adduser.

Well, suser_sid(N'COMMERCIAL DIRECT') returns a value, even though there is no login named COMMERCIAL DIRECT. Then when sp_adduser tries to run, I get an error "is not a valid login or you do not have permission".

I was able to create the login manually using a SQL query, then dropped it. suser_sid STILL returns a value for that login.

What is going on here Why does SQL Server return a sid for a login that does not exist

And no, I can't just modify the code - it's not my code, it's an app I'm implementing.

-- Paul

Raul Garcia - MS

sp_adduser depends on being able to access the (deprecated) catalog views in master DB in order to work. Some restrictions to access catalog views are new in SQL Server 2005, and old (deprecated) code does not work when there are restrictions on them.

Most likely the login exists, but the principal executing sp_adduser doesn��t have permission to access the metadata for the login. I would recommend trying the following code to verify the existence of the login:

CREATE USER [COMMERCIAL DIRECT]

go

The CREATE USER DDL doesn��t rely directly on the catalog views, and given that suser_sid returns a non-null value, it should work. If the statement above works, then most likely the principal you are using to create the users doesn��t have permission to access the catalog view.

You can also verify if the login exists using a privileged (i.e. sysadmin) account.

SELECT * FROM sys.server_principals

WHERE sid = suser_sid( N'COMMERCIAL DIRECT' )

I hope this information helps.

-Raul Garcia

SDE/T

SQL Server Engine

postwick

The CREATE USER script fails with the same error.

The issue is not a permissions issue. The login being used to do all of this is in the security administrator role, and I also tried setting the system administrator role and the problem remained.

As I said, the problem is that suser_sid returns a value even though the login is not in sysusers. Your server_principals query also returned no data.

I ran sp_addlogin and checked sysusers and server_principals and it was there. I ran sp_droplogin and it was no longer in sysusers or server_principals yet suser_sid still returnes a value.

-- Paul

Laurentiu Cristofor

Does suser_sid() return the same value when the login exists as when it doesn't Have you attempted to do a reverse lookup on the returned SID value, to see what Windows knows about it (using LookupAccountSid, see for example the code in http://blogs.msdn.com/lcris/archive/2005/09/26/474202.aspx)

Thanks

Laurentiu

postwick

Yes, the SID is the same. On Monday I'll see if that blog post helps me at all.

Thanks.

mfrei99

I have also had this issue pop up two times, on different customer machines.

I have not yet been able to reproduce this in my environment. The issue has been when we've

backed up a database and restored it onto a different SQL Server machine. Our code tests:

IF SUSER_SID(@ls_login_name) IS NULL

to determine whether or not it should create a new login. Then is tries to synchronize the user.

SUSER_SID(@ls_login_name) returns a non-null value, but the login name does not exist

in syslogins or sys.server_principals. I try sp_droplogin or DROP LOGIN and they both fail

because the login name does not exist. I think both instances happened with SQL 2005 SP2,

but I'm not sure.

I plan on changing my code that synchronizes Users to use ALTER USER ... WITH LOGIN = xxx

to simplify everything, but it is still dependant upon IF SUSER_SID(@ls_login_name) IS NULL.

Is there a better way to see if the Login exists

Thanks for any comments.

suser_sid returns value for nonexistent login

Re: suser_sid returns value for nonexistent login

Re: suser_sid returns value for nonexistent login

Re: suser_sid returns value for nonexistent login

Re: suser_sid returns value for nonexistent login

Re: suser_sid returns value for nonexistent login