best practice with logins / users

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

best practice with logins / users

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2004-08-26 : 18:59:43

I have a database server that I primarily use for just one database, however I'm throwing up a couple very small ones (1mb) to host.

I'm not sure the best way to manage logins and users, and I want to make sure I'm doing it properly before things get out of hand.

Currently in my main database "123" I have 2 users that appear as so.

Name / Login Name / DataBase Access

dbo / (blank) / Permit
mike / mike / Permit

and I have a corresponding "mike" login in the security tab.

After I restore the new database called "mup". The following appears as below:

Name / Login Name / DataBase Access

dbo / sa / Permit
mupAnon / (blank) / Permit

and I have 0 corresponding logins in the security tab.

Is one way wrong and one way right? Is one way more secure? Having sa anywhere scares me so I am trying to get it setup the same as the first.

Any suggestions are greatly appreciated.

Thanks again,
mike123

Am I approaching

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-08-26 : 19:05:57

You need to create mupAnon in the security section. Then unorphan the account after the restore. chadmat has an unorphan script for this. It's in the script library here.

And Windows Authentication is best.

Tara

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2004-08-26 : 19:11:31

Hi Tara,

Thanks for the tip. Is there anyway I can prevent this? Perhaps the way I backup the database? Or modifying the database before I back it up on the other server?

Why are there inconsistencies in the dbo login name being blank in on DB and being "sa" in another? I'm just trying to understand a little better as I haven't had much experience managing user accounts.

What do you mean by windows authentication is best ? Should I switch something ?

thanks again :) much appreciated

mike123

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-08-26 : 19:17:47

What you are using is SQL authentication. Windows Authentication is best as it's more secure. Windows Authentication would be the credentials used to log into the client machine.

You can sync the logins using sp_help_revlogin (I think that's what it is called). Derrick uses that one. It creates the proper sid. For what you are doing though, keep it simple. Just create the login, then unorphan the account. You can unorphan the account through chad's script or manually. To manually do it, just delete the account from your database. Then readd it back in. Manually is simple enough if you have only a couple of users. When you have a bunch, then you'd want to do it with a script.

Don't worry about the dbo account. It's not a real account. I mean, you can't log in with dbo as the account.

Tara

mr_mist
Grunnio

1870 Posts

Posted - 2004-08-27 : 05:05:46

sp_change_users_login is helpful in cases where you have orphaned SQL users.

-------
Moo. :)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-08-27 : 12:11:08

Right, that's what Chad's script uses.

Tara

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2004-08-27 : 17:36:53

I deleted the user and re-added them. None of my databases are too permission too, they all just have 1 user so its pretty straighttforward doing it that way.

Out of curiosity why would one dbo account have 'sa' as the login name and another have it blank ?

Thanks again Tara

mike123

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-08-28 : 01:55:11

just some info.

dbo means database owner. this is mapped to users with db_owner privileges, if it's blank then it's probably orphaned. Just delete it or use the sproc that Tara mentioned.

From experience, only the guest account has no login name, which means you can't log on with it.

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-08-30 : 08:18:30

sp_change_users_login is helpful in cases where you have orphaned SQL users.

--You don't need this if you match up the SID's in the first place between your servers. :) Following a few simple rollout processes can save you a lot of pain down the road.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

mr_mist
Grunnio

1870 Posts

Posted - 2004-08-31 : 03:00:26

quote:
Originally posted by derrickleggett

sp_change_users_login is helpful in cases where you have orphaned SQL users.

--You don't need this if you match up the SID's in the first place between your servers. :) Following a few simple rollout processes can save you a lot of pain down the road.

Hi

I'm not sure what you mean here, what process should be followed before the restore to prevent the orphaned sql users?

-------
Moo. :)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-08-31 : 12:14:59

quote:
Originally posted by mr_mist

quote:
Originally posted by derrickleggett

sp_change_users_login is helpful in cases where you have orphaned SQL users.

--You don't need this if you match up the SID's in the first place between your servers. :) Following a few simple rollout processes can save you a lot of pain down the road.

Hi

I'm not sure what you mean here, what process should be followed before the restore to prevent the orphaned sql users?

-------
Moo. :)

None. Orphaned users are going to happen. That's why you run the sp_change_users_login script after the restore.

Tara

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-08-31 : 22:55:21

No they're not Tara. If you drop all the users, run the sp_help_revlogins script on the source server, and run the corresponding script on the destination server, the next time you do a restore the SID's match up. There is no sp_change_users_login to run because the SID's are an exact match which causes this error in the first place.

This is a pain to do the first time, but the payoff is huge. After that, you need a "permission rollout process" that creates the new users, captures the sp_help_revlogin information for that user, and applies it to all the other environments. By doing this, you never have out-of-sync issues again.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-09-01 : 05:36:04

Would it not be easier and faster just to issue the sp_change_users_login rather than going through this "one-time" roll out process?

From the looks of it, you need to repeat the process for additional target servers. The load increases as you add more servers or do more restores in other servers.

Just a thought...

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-09-01 : 12:02:50

Derrick, too much pain for me to do that. It's so easy to just unorphan the accounts using a script. We have a couple of "legacy" applications that use SQL authentication for each user. The user gets added through the app without any DBA intervention. Can't have a permission rollout process for that. But it just depends on the environment I guess which method would be best.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources