Re: Script A Login
- From: "Andrew J. Kelly" <sqlmvpnooospam@xxxxxxxxxxxx>
- Date: Fri, 12 Oct 2007 20:53:30 -0400
Yes the links I posted for Phil outline this and more, specifically this one.
http://support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
But essentially this is the way it works. When you create a Windows Login it identifies that Login by using the windows account SID which is unique. When you create a user in the db and link it to the Login the SID is used as the Foreign Key value. So you can take a DB from any server and plop it down (restore it) on any other server (assuming the same Domain etc) and the two will always match up. Even if the new server did not have the Login to begin with when you create the Login it will use the same SID as the other server since it gets it from AD not SQL Server. But when you create a SQL Login it uses an Integer that just happens to be the next one available on the server. So if you restore a db to another server (or sql instance) the ID of the SQL Login may or may not be the same as the other server. But the ID of the original user is persisted in the db and is there when you restore it. But now the link between the two may or may not be the same. You can not count on it being the same and should not. The articles show how to deal with them. Hope that helps.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Richard Mueller [MVP]" <rlmueller-nospam@xxxxxxxxxxxxxxxxxxxx> wrote in message news:eIarL$SDIHA.1208@xxxxxxxxxxxxxxxxxxxxxxx
Yes, I do use Windows Authentication, but I am being asked to convert to SQL Logins so I need to understand this. When I restore a database, all of the database User Accounts and Database Roles are restored. When I restore a backup (on the same server), are you saying the connection between the SQL Login in the server and the User Account in the database is broken?
Using Windows Authentication I have been able to restore databases on new servers and everything works. The restored database has the database user accounts and roles. Will the link between the SQL Login and database user account need to be established in that case?
--
Richard Mueller
Microsoft MVP Scripting and ADSI
Hilltop Lab - http://www.rlmueller.net
--
"Andrew J. Kelly" <sqlmvpnooospam@xxxxxxxxxxxx> wrote in message news:uruSOdPDIHA.3916@xxxxxxxxxxxxxxxxxxxxxxxRichard,
Then you must be using all Windows Logins or are just getting lucky. If you use SQL Logins the ID's that map the Login to the Users may not be the same on each server. In that case sp_change_users_login works wonders.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Richard Mueller [MVP]" <rlmueller-nospam@xxxxxxxxxxxxxxxxxxxx> wrote in message news:ucsGxXODIHA.3884@xxxxxxxxxxxxxxxxxxxxxxxPhil wrote:
I am crrently running SQL Server 2005, I have an Excel VBA application that
exacutes a number of stored procedures against the database, this database is
restored every evening so needs a login created on it every time the restore
job runs. I believe the script should look a little like this for creating a
Login and User
USE [Database]
CREATE LOGIN ExcelKPI WITH password='ExcelKPI',
Default_Database = [Database]
CREATE USER ExcelKPI FOR LOGIN ExcelKPI
WITH DEFAULT_SCHEMA=dbo_owner
And then I need to Grant necessary permissions, e.g. Insert & Update
It runs fine and creates the logins but still doesn't work, I have to then
go to the main security tab and give the login Sysadmin rights to get it to
work which I am sure shouldn't be necessary, I am probably just not setting
it up correctly.
Could anyone offer some advice.
I restore databases all the time and don't need to re-create logins or users. The SQL Login is in the instance, not the database, so it should be unaffected by the restore. The user account should have been in the database that was backed up, so when you restore everything works. If you use a database role to assign permissions to the user account, this role is also restored, so all permissions are restored. The backup you use for the restore should have the user account and database role. If not, you need to re-create them.
--
Richard Mueller
Microsoft MVP Scripting and ADSI
Hilltop Lab - http://www.rlmueller.net
--
.
- References:
- Re: Script A Login
- From: Richard Mueller [MVP]
- Re: Script A Login
- From: Andrew J. Kelly
- Re: Script A Login
- From: Richard Mueller [MVP]
- Re: Script A Login
- Prev by Date: Re: Script A Login
- Next by Date: Re: Fixed Database roles do not appear for database tables Permissions
- Previous by thread: Re: Script A Login
- Next by thread: Re: Script A Login
- Index(es):
Relevant Pages
|
|
