Hi,

Could anyone help me with the following?

I am writing an SQL script to create a new database on an existing MS SQL Server (2000). To this end the master database on the server is accessed with the user 'sa' to obtain sysadmin rights. The database is then created according to the script included below. Next, I want to create a new login for SQL Server authentication, that should finally be the only user (besides 'sa') that can access the database. This could be done by:

sp_addlogin 'appname', 'pwd', 'dbname'

Works so far, but here I get stuck. When connecting to the SQL server using 'uid=appname;pwd=pwd;', the user apperently does not have access rights for the database 'dbname' yet. My question: How (through SQL) can you set the permission properties for the user 'appname' such that it gets exclusive full rights for the database?

With best regards,
Sander



F.Y.I. Here is the script used to create the database

CREATE DATABASE [dbname] ON (NAME = N'dbname_dat', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL\data\dbname.mdf' , SIZE = 4, FILEGROWTH = 10%) LOG ON (NAME = N'dbname_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL\data\dbname.ldf' , SIZE = 2, FILEGROWTH = 10%)
COLLATE SQL_Latin1_General_CP1_CI_AS
GO

exec sp_dboption N'dbname' , N'autoclose', N'false'
exec sp_dboption N'dbname' , N'bulkcopy', N'true'
exec sp_dboption N'dbname' , N'trunc. log', N'true'
exec sp_dboption N'dbname' , N'torn page detection', N'true'
exec sp_dboption N'dbname' , N'read only', N'false'
exec sp_dboption N'dbname' , N'dbo use', N'false'
exec sp_dboption N'dbname' , N'single', N'false'
exec sp_dboption N'dbname' , N'autoshrink', N'false'
exec sp_dboption N'dbname' , N'ANSI null default', N'false'
exec sp_dboption N'dbname' , N'recursive triggers', N'false'
exec sp_dboption N'dbname' , N'ANSI nulls', N'false'
exec sp_dboption N'dbname' , N'concat null yields null', N'false'
exec sp_dboption N'dbname' , N'cursor close on commit', N'false'
exec sp_dboption N'dbname' , N'default to local cursor', N'false'
exec sp_dboption N'dbname' , N'quoted identifier', N'false'
exec sp_dboption N'dbname' , N'ANSI warnings', N'false'
exec sp_dboption N'dbname' , N'auto create statistics', N'true'
exec sp_dboption N'dbname' , N'auto update statistics', N'true'
GO

USE dbname
GO

CREATE TABLE Accounts (
    AccountID tinyint IDENTITY (0, 1) NOT NULL ,
    Naam nvarchar (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
..... etc

As you have seen sp_addlogin just gives the account a logon to the db server. You need sp_grantdbaccess to get into specific databases. Also you will probably need sp_addrolemember to control the access rights within each db. Alternatively you can give specific object level permissions using the T-SQL GRANT or DENY statements.

hth
Phil

Thanks,

Using sp_grantdbaccess and sp_addrolemember it was easy to provide db_owner privileges for the user. Here's the script that works for me:

--Create a SQL Server login
USE master
EXEC sp_addlogin 'appname','pwd', 'dbname'
USE dbname

--Remove guest access from the database, if necessary
IF exists (SELECT * FROM sysusers WHERE name='guest' AND status<>0)
EXEC sp_revokedbaccess 'guest'

--Grant database access for the new login with db_owner privileges
IF NOT exists (SELECT * FROM sysusers WHERE name='MiceBase')
EXEC sp_grantdbaccess 'MiceBase'
EXEC sp_addrolemember db_owner,'MiceBase'