SQL server login

Last updated: 2014-05-28

I guess I am not the only one, who thinks the SQL user account setup is a strange mess.

So I tried to draw a picture. This is what came out untill now:

In SQL 2005 and onwards the permission hierarchy consists of securables: http://msdn.microsoft.com/en-us/library/ms190401.aspx.

Found this confusing picture:

Database Engine permissions hierarchies

To setup the account one could use some of this SQL:

-- Create a Db
CREATE DATABASE [MyDb] ON PRIMARY
( NAME = N'MyDb', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\MyDb.mdf' , SIZE = 3072KB , FILEGROWTH = 10%)
LOG ON
( NAME = N'MyDb_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\MyDb_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
GO

-- Create a login
CREATE LOGIN [MyAccount] WITH PASSWORD=N'p@ssw0rd', DEFAULT_DATABASE=[MyDb], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
--or: EXEC master.dbo.sp_addlogin @loginame = N'MyAccount', @passwd = N'p@ssw0rd', @defdb = N'MyDb', @deflanguage = N'us_english'

-- Create user in [MyDb], if it does not exist
USE [MyDb]
GO
CREATE USER [MyAccount] FOR LOGIN [MyAccount] WITH DEFAULT_SCHEMA=[dbo]
--or: EXEC dbo.sp_grantdbaccess @loginame = N'MyAccount', @name_in_db = N'MyAccount'

-- Optionally create roles in [MyDb], if they are needed
USE [MyDb]
GO
CREATE ROLE [MyDbUsers] AUTHORIZATION [dbo]
-- or: EXEC dbo.sp_addrole @rolename = N'MyDbUsers', @ownername = N'dbo'

-- Guess following will authorize the role to be able to do CRUDs on all tables, views, sp's etc, but I'm not sure
ALTER AUTHORIZATION ON SCHEMA::[db_datareader] TO [MyDbUsers]
ALTER AUTHORIZATION ON SCHEMA::[db_datawriter] TO [MyDbUsers]
GO

-- Give the user roles
USE [MyDb]
GO
EXEC sp_addrolemember @rolename = N'public', @membername = N'MyAccount'
EXEC sp_addrolemember @rolename = N'db_ddladmin', @membername = N'MyAccount'
EXEC sp_addrolemember @rolename = N'MyDbUsers', @membername = N'MyAccount'
GO

-- grant rights to users and roles
GRANT EXEC ON MySp TO N'MyDbUsers' -- PUBLIC
GO

Nice to have for a script.

For generating script or entering into only one db the wizards from SQL Srv Mgmt Studio (SSMS) can be used:

– Create a DB:

– Create a Login:

You should change the default db (ie. to MyDb), if you want your SSMS to connect to a certain db after you have logged into your instance of a SQL server. Then you can fire your SQL script without having to choose db first.

– Create a db user and tie it to a sql server login:

Notice default schema and owned schemas.

http://msdn.microsoft.com/en-us/library/ms190387.aspx: “A schema is simply a container of objects”. The objects are tables etc.

The default schema is the direct link to to the objects without going through a role. New in SQL 2005 is that instead of having a link for each user then more users can share the same link. The default schema is dbo.
This must mean that if we want to do like in pre SQL 2005 then we should give the default schema the same name as the user.

Owned schemas is a way to make a namespace for the contained objects:

A schema is a collection of database objects that are owned by a single user and form a single namespace. A namespace is a set of objects that cannot have duplicate names. For example, two tables can have the same name only if they are in separate schemas. A database object such as a table is owned by a schema, and the schema is owned by a database user, or role. When the owner of a schema leaves the organization, transfer the ownership of the schema to a new user or role before the departing user is deleted.

OK then the schema db_datareader must be owned by the role with the same name (db_datareader). A user can then be member of that role. Maybe a user can own a schema which contains the role db_datareader?

Maybe one should think of a schema like a wallet containing shared membeship cards and that wallet can be shared by people with the same role?

Or maybe just leave it as the italic sentence above :-).

Create a new role with exec permission to all stored procedures in a DB

Thanks to Per Andersen for following script:
-- =============================================
-- Script Template
-- Create a new role with exec permission to all stored procedures in a DB
-- Optionally create a new System account
-- Add System account to new role
-- =============================================

DECLARE
@TabName                         varchar(100),
@SPName                          varchar(100),
@ViewName                        varchar(100),
@DataBaseName                    varchar(100),
@NewRole                         varchar(100),
@NewUser                         varchar(100),
@NewUserPassword                 varchar(100)

-- init
set @DataBaseName                = 'My_System_DB'
set @NewRole                     = 'my_spexec'
set @NewUser                     = 'mydomain\mysystemaccount'
--set @NewUserPassword             = 'mypsw'

IF NOT EXISTS (SELECT * FROM dbo.sysusers WHERE name = @NewRole AND uid > 16399)
           EXEC sp_addrole @NewRole

-- Tables
DECLARE TabCursor CURSOR FAST_FORWARD LOCAL FOR
SELECT TABLE_NAME FROM information_schema.tables WHERE TABLE_TYPE='BASE TABLE'
OPEN TabCursor

FETCH NEXT FROM TabCursor INTO @TabName
WHILE @@FETCH_STATUS = 0
           BEGIN
                      EXEC ('GRANT ALL ON [' + @TabName + '] TO [' + @NewRole + ']')
                      FETCH NEXT FROM TabCursor INTO @TabName
           END
CLOSE TabCursor
DEALLOCATE TabCursor
-- Views
DECLARE VCursor CURSOR  LOCAL FOR
SELECT TABLE_NAME FROM information_schema.views
OPEN VCursor

FETCH NEXT FROM VCursor INTO @ViewName
WHILE @@FETCH_STATUS = 0
           BEGIN
                                 EXEC ('GRANT ALL ON [' + @ViewName + '] TO [' + @NewRole + ']')
                                 FETCH NEXT FROM VCursor INTO @ViewName
           END
CLOSE VCursor
DEALLOCATE VCursor

-- Procedures
DECLARE SCursor CURSOR FAST_FORWARD LOCAL FOR
SELECT name FROM sysobjects WHERE type = 'p'
OPEN SCursor

FETCH NEXT FROM SCursor INTO @SPName
WHILE @@FETCH_STATUS = 0
           BEGIN
                      EXEC ('GRANT ALL ON [' + @SPName + '] TO [' + @NewRole + ']')
                      FETCH NEXT FROM SCursor INTO @SPName
           END
CLOSE SCursor
DEALLOCATE SCursor

-- optionally create user
--EXEC sp_addlogin @NewUser, @NewUserPassword, @DataBaseName
--EXEC sp_grantdbaccess @NewUser, @NewUser

--- Add user to the new role
EXEC sp_addrolemember @NewRole, @NewUser

Give an AD group dbo access

If you are using Windows login, then it can be useful to give an AD group dbo permission.

--Give an AD group dbo access
USE [master]
GO
CREATE LOGIN [MyDomain\MyADGroup] FROM WINDOWS --WITH DEFAULT_DATABASE=[MyDb]
GO

USE [MyDb]
GO
CREATE USER [MyDomain\MyADGroup] FOR LOGIN [MyDomain\MyADGroup] --WITH DEFAULT_SCHEMA=[dbo]
GO

USE [MyDb]
GO
EXEC sp_addrolemember N'db_owner', N'MyDomain\MyADGroup'
GO

 

The End.
Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: