Re: Script A Login
- From: "Richard Mueller [MVP]" <rlmueller-nospam@xxxxxxxxxxxxxxxxxxxx>
- Date: Fri, 12 Oct 2007 10:37:16 -0500
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
- Prev by Date: Re: AD groups and auditing
- Next by Date: Re: Script A Login
- Previous by thread: Fixed Database roles do not appear for database tables Permissions
- Next by thread: Re: Script A Login
- Index(es):
Relevant Pages
|
