SQL Script to Create Roles,Schemas, Add users and Associate Schema to Role

/* SQL Script to Create Roles, Schemas and users to the same */

--Creation of Database Role and Adding User's begin

USE [YOURDATABASENAME]

GO

CREATE ROLE [YOURROLENAME]

GO

      --Adding Users Begin

      EXEC sp_addrolemember N'YOURROLENAME', N'USERNAME1'

      GO

      EXEC sp_addrolemember N'YOURROLENAME', N'USERNAME2'

      GO

      --Adding Users End

      --Similarly Multiple users can be added using EXEC sp_addrolemember

--Creation of Database Role and Adding User's End

--Creation of Schema Starts

USE [YOURDATABASENAME]

GO

CREATE SCHEMA [YOURSCHEMANAME] AUTHORIZATION [YOURSCHEMANAME]

GO

--Creation of Schema End

--Association of Created Schema to Role Starts

USE [YOURDATABASENAME]

GO

ALTER AUTHORIZATION ON SCHEMA::[YOURSCHEMANAME] TO [YOURROLENAME]

GO

--Association of Created Schema to Role End

SQL Server Agent Proxies

SQL Server Agent Proxies are very helpful in executing some jobs. I had came across a situation where in I was scratching my head so as to how to accomplish a task which I wanted to run as SQL server Agent Job. First let me give some brief description about proxies.

SQL Server Agent uses proxies to manage security contexts. A proxy can be used in more than one job step. A SQL Server Agent proxy defines the security context for a job step. A proxy provides SQL Server Agent with access to the security credentials for a Microsoft Windows user. A job step that uses the proxy can access the specified subsystems by using the security context of the Windows user. Before SQL Server Agent runs a job step that uses a proxy, SQL Server Agent impersonates the credentials defined in the proxy, and then runs the job step by using that security context.

Now lemme give you a small example where how exactly a proxy can be helpful.

Say you have around 10 database servers,  both SQL Engine and SQL Agent services on these servers run on different Service Account say Machine Specific. Ex Ser1, Ser2, Ser3 and so on. Now here is the task. You want some operations may be a T-SQL or some script combined into SSIS package to be run on all these services. One way to achieve this is schedule a Job on each of these server and what if you want a consolidated data like health of all servers or logins existing on these servers as single report. This is where your Proxy comes in handy. To achieve this task get a Service Account(A windows Domain user Account), give access to this service account on all servers and then create a credential for this service account and create proxy for this credential on any of the one server and execute this Job or package from one single server and get combined data on one server. By doing so what exactly happens is SQL server agent impersonates the associated User account with proxy against the server on which the script/job needs to be run and since you have provided access to this service account on all server you will be able to run the same. Now lemme guide you through a step wise procedure to create a proxy using T-SQL.

--Detailed Steps to create a SQL proxy using T-SQL
--Step 1 - Create a credential for proxy

USE MASTER
GO
--Drop and create the demo credential if it is already existing
IF EXISTS (SELECT * FROM sys.credentials WHERE name = N'ProxyDemoCredentials')
BEGIN
DROP CREDENTIAL [ProxyDemoCredentials]
END
GO
CREATE CREDENTIAL [ProxyDemoCredentials]
WITH IDENTITY = N'YourDomain\ProxyDemoServiceAccount',
SECRET = N'ServiceAccountPasswordHere'
GO 
--End of Step 1

--Step 2 - Create a demo proxy account
USE msdb
GO
--Drop the demo proxy if it is already existing
IF EXISTS (SELECT * FROM msdb.dbo.sysproxies WHERE name = N'ProxyDemo')
BEGIN
  EXEC dbo.sp_delete_proxy
    @proxy_name = N'ProxyDemo'
END
GO
--Creating a demo proxy and assigning the demo credential to the same
EXEC msdb.dbo.sp_add_proxy
  @proxy_name = N'ProxyDemo',
  @credential_name=N'ProxyDemoCredentials',
  @enabled=1
GO
--You Need to Enable a Proxy before using it which can be done using below step.
EXEC msdb.dbo.sp_update_proxy
  @proxy_name = N'ProxyDemo',
  @enabled = 1 
GO 
--End of Step 2

--Step 3 Granting created demo proxy to SQL Agent subsystem
EXEC msdb.dbo.sp_grant_proxy_to_subsystem
  @proxy_name=N'ProxyDemo',
  @subsystem_id=11 --subsystem 11 is for SSIS. You can get info using EXEC sp_enum_sqlagent_subsystems 
GO
--End of Step 3

--Step 4 Granting access of security principals(service account) to demo proxy created
USE msdb
GO
--Grant proxy account access to security principals
EXEC msdb.dbo.sp_grant_login_to_proxy
  @proxy_name=N'ProxyDemo'
  ,@login_name=N'YourDomain\ProxyDemoServiceAccount'
  --,@fixed_server_role=N'<fixed_server_role name>' if any roles
  --,@msdb_role=N'<msdb_role name>' if any MSDB roles
GO
--End of Step 4.

Now just use this proxy under Run AS in job step for using SSIS package.

Technorati Tags: proxy,credintials,Login,Proxies,SSIS

Creating A Role and Assigning permission

As a DBA we will be having many day to day tasks. One of which may be assigning roles for users to database.  This is pretty easy if you want to create just one or two users and assign or create one or two roles to the same. Now, what if there are 100 logins to be created for different databases and create multiple roles and grant access to the same.  Now we can easily achieve this by just creating a group in domain level in Active Directory for each role you want (Please contact your system Administrator if you are not enough well versed with Active Directory) and add all the users to this group and just create role for this Domain group and grant the access you want for this role. For this this is what I do. I have a T-SQL script which makes my life easy in such cases. You can find the script as mentioned below.

/* Create a login which will Domain Group

   created in Active Directory */

USE [master]

GO

/* I prefer using naming convention

   which will be easy for tracking

   Where MyDomain is your Domain and

   RoleName is the Role/Roles you want

   to be granted permissions and

   YourDatabase is your database name*/

CREATE LOGIN [MyDomain\RoleName_Securables] FROM WINDOWS WITH DEFAULT_DATABASE=[master]

GO

/* Create/Map user to database

   on which you want the role permissions */

USE [YourDatabase]

GO

CREATE USER [MyDomain\RoleName_Securables] FOR LOGIN [MyDomain\RoleName_Securables]

GO

/* Create Role and Role Member to Your login

   created and grant permission to same. Here for example

   i have taken VIEW DEFINITION Role, where RoleName is the name

   of the role */

USE [YourDatabase]

GO

CREATE ROLE [RoleName]

GO

USE [YourDatabase]

GO

EXEC sp_addrolemember N'RoleName', N'MyDomain\RoleName_Securables'

GO

use [YourDatabase]

GO

GRANT VIEW DEFINITION TO [RoleName]

GO

We are all set just replace the conventions used above with your actual values and execute it in a Query Browser.

Note you can also do this for individual account which may be either Domain Account or SQL Login. All you need to do is replace MyDomain\RoleName_Securables with the Domain Account or SQL Login.

Make sure you replace the Windows Group login creation script

/* Create a login which will Domain Group

   created in Active Directory */

USE [master]

GO

/* I prefer using naming convention

   which will be easy for tracking

   Where MyDomain is your Domain and

   RoleName is the Role/Roles you want

   to be granted permissions and

   YourDatabase is your database name*/

CREATE LOGIN [MyDomain\RoleName_Securables] FROM WINDOWS WITH DEFAULT_DATABASE=[master]

GO

With below mentioned script

/* For creating a SQL login */

USE [master]

GO

CREATE LOGIN [MySQLLogin] WITH

PASSWORD=N'StrongPasswordHere'

MUST_CHANGE,

DEFAULT_DATABASE=[master],

CHECK_EXPIRATION=ON,

CHECK_POLICY=ON

GO