Is the no.of users in each database must be equal or less the no.of logins in the Security tab ?

Question

Is the no.of users in each database must be equal or less the no.of logins in the Security tab ?

rambilla4

SSCrazy Eights

Points: 8715
More actions
July 22, 2009 at 3:13 pm

#385035

Hello,
Is the no.of users in each database must be equal or less the no.of logins in the Security tab ?
Lets say I have 5 Logins in Security tab, I can have only these 5 logins as users in any database and less than these 5 logins But not any users other than these logins right?
Because, I can see in my database with 15 users but there are only 2 logins(these 2 logins aslo there in the 15 users. I mean these 2 logins + 13 other users) in the Security tab? Is this possible?
Thanks

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

each login could connect to your database an unlimited number of timse; as an example, if you've ever had two or more queries going on in SSMS, each "page" has it's own connection(spid), all using the same login

so while you could potentially have NO users defined in the database, a lot of people could connect as "sa", and you'd see lots of connections.

does that help?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

rambilla4 SSCrazy Eights Points: 8715 More actions · Answer 2

thanks you,

I have 2 logins as below in sql server 2000

abc

abc123

Usrer in the databases:

abc

abc123

scott

john

steve

alex

dbo

sys

guest(disabled)

Now I have upgraded this database to sql server 2005. Befor Upgrade I scripted all the logins in 2000 and after upgrade I ran this script and the logins were created in 2005. Now I'm checking for the Orphaned users.

SP_CHANGE_USERS_LOGIN 'REPORT' and i got the below orphaned users

UserName UserId

abc 0xF02ACE040B812C4CA55D9A7362C4CA5A

dbo 0xF02ACE040B812C4CA55D9A7360B923CB

alex 0xF02FCE040B812C4CA55D9A74602C4CA5

Fixing Orphand users:

sp_change_users_login 'auto_fix','user'

and I'm able to fix the user abc only. When try to fix the rest of the users I,m getting the below error:

Msg 15600, Level 15, State 1, Procedure sp_change_users_login, Line 207

An invalid parameter or option was specified for procedure 'sys.sp_change_users_login'.

Please advice me...

Greg Charles SSC-Forever Points: 45403 More actions · Answer 3

The Auto-Fix argument will map orphaned users to logins with the same name. Since you only have a login for abc, that's the only one that can be fixed.

It seems that all the logins didn't get scripted in SQL 2000. Is the SQL 2000 instance still available so you could script them again? If all else fails, you can manually create the missing logins in SQL 2005 and map them to the users with sp_change_users_login.

Greg

rambilla4 SSCrazy Eights Points: 8715 More actions · Answer 4

I have just checked in sql 2000 and we have ONLY 2 logins abc &abc123 nothing else. So I'm good to go or this error will stop working the application?

I'm getting the problem for the below 3 logins only.And out of the 3, I'm able to fix abc. I not able to fix dbo and Alex.Should I ignore these 2? will impact anything? But in Logins, there is no dbo and Alex. Only abc & abc123

SP_CHANGE_USERS_LOGIN 'REPORT' and i got the below orphaned users
UserName UserId
abc 0xF02ACE040B812C4CA55D9A7362C4CA5A
dbo 0xF02ACE040B812C4CA55D9A7360B923CB
alex 0xF02FCE040B812C4CA55D9A74602C4CA5

thanks for your help

Greg Charles SSC-Forever Points: 45403 More actions · Answer 5

Whether this situation affects your application depends on what login the app is using to connect to SQL Server.

DBO is the owner of the database and is always mapped to a login, though not to a login named DBO. All of our databases are owned by sa. What do you see for that when you look at DBO's properties in SQL Server Management Studio?

If Alex is used by the application to connect to SQL Server, you'll need to add the login and map the database user to it.

Greg

rambilla4 SSCrazy Eights Points: 8715 More actions · Answer 6

Thank you very much,

I have attached the User dbo properties. I have this dbo user in all databases. But I'm getting this dbo user as a orphaned user in only one database.

thanks

rambilla4 SSCrazy Eights Points: 8715 More actions · Answer 7

rambilla4

SSCrazy Eights

Points: 8715

July 24, 2009 at 11:48 am

#1029938

Hi Greg,

could you please advice me..

thanks

Greg Charles SSC-Forever Points: 45403 More actions · Answer 8

Sorry, I've been on vacation. Hopefully you've figured this out, but if not, it occured to me that maybe the new instance was set up in Windows authentication mode. If that's true, then the sa account is disabled and that's why dbo in that database is reported as an orphan user. I would expect that dbo in the other databases is mapped to some other login(s).

You can either switch to mixed mode and activate the sa account or you can map dbo in that database to another, existing login.

Greg