Creating SQL Server databases..

Okay, I'm fairly new to administering SQL server databases and was wondering what you guys use for your scripts when creating new accounts/logins/permissions when creating databases for users who should have access to adding/deleting/editing/selecting tables from within their own database?

What I am thinking is that you create the database with the defined files sizes and growth permissions, but what then? Do you create a login and use the sp_addrolemember? My best guess would be as is below...

USE master
GO
CREATE DATABASE DevDatabase ON PRIMARY
(
NAME = DevDatabase_Data,
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL\data\DevDatabase_Data.mdf',
SIZE = 20MB,
MAXSIZE = 50MB,
FILEGROWTH = 10MB
)
LOG ON
(
NAME = DevDatabase_Log,
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL\data\DevDatabase_Log.ldf',
SIZE = 5MB,
MAXSIZE = 10MB,
FILEGROWTH = 1MB
)
GO
EXEC sp_addlogin 'NewUserName', 'NewUserPassword'
GO
USE DevDatabase
GO
EXEC sp_addrolemember 'db_ddladmin', 'NewUserName'
GO

Thanks for any help / suggestions as I have found multiple ways to grant access to a database, but want to make sure to keep the SQL server set up with the correct permissions for security issues.

Andrew

 

 

 

 

Top