Techlithium: Creating a login user in a SQL Server 2008 database and adding permissions to the dbo.schema in SQL Server 2008.

Monday, July 29, 2013

Creating a login user in a SQL Server 2008 database and adding permissions to the dbo.schema in SQL Server 2008.

USE [TestDB]

CREATE LOGIN TestUser1

WITH PASSWORD = N'TestPassword1#',

CHECK_POLICY = OFF,

CHECK_EXPIRATION = OFF;

CREATE USER TestUser1 FOR LOGIN TestUser1;

-- add user to db_owner

Exec Sys.sp_addrolemember 'db_owner', 'TestUser1';

-- add user to db_accessadmin

Exec Sys.sp_addrolemember 'db_accessadmin', 'TestUser1';

-- add user to db_datareader

Exec Sys.sp_addrolemember 'db_datareader', 'TestUser1';

-- add user to db_datawriter

Exec Sys.sp_addrolemember 'db_datawriter', 'TestUser1';

-- add user to db_ddladmin

Exec Sys.sp_addrolemember 'db_ddladmin', 'TestUser1';

GRANT INSERT ON SCHEMA :: dbo TO TestUser1;

GRANT Alter ON SCHEMA :: dbo TO TestUser1;

GRANT DELETE ON SCHEMA :: dbo TO TestUser1;

GRANT SELECT ON SCHEMA :: dbo TO TestUser1;

GRANT UPDATE ON SCHEMA :: dbo TO TestUser1;

GRANT INSERT ON SCHEMA :: dbo TO TestUser1;

Techlithium