April 16, 2007 at 10:07 am
I have some database users which have no associated logins. The user name is CanHamUser with login name 'None' and Database access as 'permit'. How is it possible? As per my knowledge user cannot be created without login name.
April 16, 2007 at 10:16 am
Was this database attached or restored from another instance? Users go with a database when it's moved, but logins don't.
Greg
Greg
April 16, 2007 at 10:28 am
Oh thats what i am thinking. Yes the database was restored from production to testing environment. So users with blank logins are void and we can delete those users from database. Thanks Greg
April 16, 2007 at 2:40 pm
Look up Orphaned Users in the BOL. That will show you a script to run to find out which logins are 'orphaned'.
-SQLBill
April 17, 2007 at 7:16 am
They are probably orphaned users as others stated. However, if this is SQL2005, you can create users without logins with the "WITHOUT LOGIN" option in CREATE USER. This new type of login-less user can be used as part of a security model.
April 17, 2007 at 7:22 am
If you need to synch up users/logins after a restore, you can:
USE DATABASENAME
go
EXEC sp_change_users_login 'Update_One', ‘username’, ‘login’'
[where ‘DATABASENAME’ = the name of the database; ‘username’ = the name of the user, and ‘login’ = the user’s SQL Server login name]
For more information see: http://support.microsoft.com/default.aspx?scid=kb;en-us;246133&Product=sql
Norene Malaney
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply