Re: Script A Login

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
--



.

Relevant Pages

  • Re: Script A Login
    ... Yes, I do use Windows Authentication, but I am being asked to convert to SQL ... When I restore a database, ... database User Accounts and Database Roles are restored. ... Login in the server and the User Account in the database is broken? ...
    (microsoft.public.sqlserver.security)
  • Re: Script A Login
    ... exacutes a number of stored procedures against the database, ... Login and User ... I restore databases all the time and don't need to re-create logins or ... The user account should have been in the database ...
    (microsoft.public.sqlserver.security)
  • Re: SQL 2000 SP3 DB User Login Name changes
    ... already a user in the database'. ... >> I'm not sure why you have different behavior with attach vs. restore. ... Login Name ... >>> attach and the sysdba user doesn't have any Login Name. ...
    (microsoft.public.sqlserver.security)
  • Re: "Best Practices" way to distribute MSDE
    ... Ways to put a database into a server: ... Restore a users's selected backup. ... if i'm supposed to be using MSDE now instead of Jet: ... > if you can't connect with that login, ...
    (microsoft.public.sqlserver.msde)
  • Re: SQL 2000 SP3 DB User Login Name changes
    ... database to a different server. ... I'm not sure why you have different behavior with attach vs. restore. ... > [Notice that the dbo user incorrectly has 'sysdba' as a Login Name after ...
    (microsoft.public.sqlserver.security)