DB Install Script

Question

DB Install Script

jesse.bryant

Newbie

Points: 1
More actions
September 6, 2008 at 10:13 pm

#197401

Hey everyone,
I'm trying to make an installation script which sets up my database to be used only by the login/user it creates, and denies any other windows authentication. I have the following:
Create Database DB_Name;
GO
USE [DB_Name]
GO
CREATE LOGIN [logDB_Name] WITH PASSWORD='D9hf36q', DEFAULT_DATABASE=[DB_Name], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON
GO
USE [DB_Name]
GO
CREATE USER [usrDB_Name] FOR LOGIN [logDB_Name] WITH DEFAULT_SCHEMA=[db_owner]
GO
USE [DB_Name]
GO
ALTER AUTHORIZATION ON SCHEMA::[db_owner] TO [usrDB_Name]
GO
USE [DB_Name]
GO
EXEC sp_addrolemember N'db_securityadmin', N'usrDB_Name'
GO
USE [DB_Name]
GO
EXEC sp_addrolemember N'db_owner', N'usrDB_Name'
GO
...Create tables...
EXEC sp_changedbowner 'usrDB_Name', False
I had this working, but when it was, it had the opposite effect, and only allowed the windows authentication use the database, but not the login/user it made.
Can I get some help?
Thanks,
Jesse.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

Is the SQL Server setup to use Mixed Authentication?

By default only members of the sysadmin role would have rights within database when you create and then any logins you add to the database.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Glen SSCertifiable Points: 6737 More actions · Answer 2

BOL for sp_changedbowner:

[@loginame = ] 'login'

Is the login ID of the new owner of the current database. login is sysname, with no default. login must be an already existing SQL Server login or Microsoft Windows user. login cannot become the owner of the current database if it already has access to the database through an existing alias or user security account within the database. To avoid this, drop the alias or user within the current database first.

It is the login that your are mapping as an owner of a database, not a user?

If you want to grant a ownership on the DB to a Login, it should be:

sp_changedbowner login_name

chuck59 Newbie Points: 6 More actions · Answer 3

I would like to add my 2cents here 🙂

I created below script a long time ago having a lot of readings about database security... this is being used on our hosting servers and is **tested**...

sp_addlogin '{uid}', '{pwd}', 'pubs'

GO

CREATE DATABASE {db}

GO

sp_defaultdb @loginame = '{uid}', @defdb = '{db}'

GO

USE master

GO

ALTER DATABASE {db} SET RESTRICTED_USER

ALTER DATABASE {db} SET RECOVERY SIMPLE

ALTER DATABASE {db} SET ANSI_NULLS ON

ALTER DATABASE {db} SET ANSI_PADDING ON

ALTER DATABASE {db} SET ANSI_WARNINGS ON

ALTER DATABASE {db} SET RECURSIVE_TRIGGERS ON

ALTER DATABASE {db} SET AUTO_UPDATE_STATISTICS ON

ALTER DATABASE {db} SET TORN_PAGE_DETECTION ON

ALTER DATABASE {db} SET AUTO_SHRINK ON

ALTER DATABASE {db} SET AUTO_CREATE_STATISTICS ON

ALTER DATABASE {db} SET QUOTED_IDENTIFIER ON

ALTER DATABASE {db} SET AUTO_CLOSE ON

ALTER DATABASE {db} SET CURSOR_CLOSE_ON_COMMIT ON

ALTER DATABASE {db} SET ARITHABORT ON

ALTER DATABASE {db} SET CONCAT_NULL_YIELDS_NULL ON

ALTER DATABASE {db} SET ONLINE

GO

USE master

GO

ALTER DATABASE {db} MODIFY FILE (NAME={db}, MAXSIZE={dbsize}MB, FILEGROWTH=10%)

GO

ALTER DATABASE {db} MODIFY FILE (NAME={db}_Log, MAXSIZE={logsize}MB, FILEGROWTH=10%)

GO

USE {db}

GO

sp_changedbowner '{uid}'

GO

I would like to hear your comments.