Re: Script A Login
- From: "Richard Mueller [MVP]" <rlmueller-nospam@xxxxxxxxxxxxxxxxxxxx>
- Date: Fri, 12 Oct 2007 19:26:23 -0500
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@xxxxxxxxxxxxxxxxxxxxxxx
Richard,
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@xxxxxxxxxxxxxxxxxxxxxxx
Phil 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
--
.
- Follow-Ups:
- Re: Script A Login
- From: Andrew J. Kelly
- Re: Script A Login
- References:
- Re: Script A Login
- From: Richard Mueller [MVP]
- Re: Script A Login
- From: Andrew J. Kelly
- Re: Script A Login
- Prev by Date: Re: Fixed Database roles do not appear for database tables Permissions
- Next by Date: Re: Script A Login
- Previous by thread: Re: Script A Login
- Next by thread: Re: Script A Login
- Index(es):
Relevant Pages
|
