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 

SQL Server Agent Not getting started after a Restart of Services Post Fresh Installation:

There are times where the SQL Server and SQL Server Agent Services are running fine post installation but your SQL Server Agent might not start after a restart of your SQL Server and SQL Server Agent services post installation. There may be many causes out of which the one which I am discussing is one.

Root Cause for the Problem:

We have a tendency to change the SQLAgent.Out file default location to some specific location as per out convenience. We may use EXEC master..xp_instance_regwrite to do the same.

Unfortunately in SQL Server 2008 no doubt this will be written in Registry but will not be updated in SQL Server Agent Properties.

Resolution:

This can be resolved by either changing the ErrorLogFile path in Registry to the one which we have configured in SQL Server Agent properties.

Ideally you can find this ErrorLogFile registry key in path

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.MSSQLSERVER\SQLServerAgent\ErrorLogFile.

Hope this might be of some help

Unable to Shrink/FreeUp TempDB Space

Sometimes we come across situation where in the Entire Space in TempDB will be unallocated space but we are unable to free up space neither we are able to shrink the database or file. This was how I was able to Free Up the unallocated space and it worked for me. So thought of sharing it.

USE TempDB

GO

DBCC FREEPROCCACHE
GO
DBCC DROPCLEANBUFFERS
go
DBCC FREESYSTEMCACHE ('ALL')
GO
DBCC FREESESSIONCACHE
GO
dbcc shrinkfile (<TempDBLogicalFileName>,SizeInMB)
GO

Note: Logical File name can be obtained using SP_HELPFILE

Updating Email ID for Alerts and Mail Items

Recently I came across a situation where in I had to change the Email ID for all alerts and Mail Items on multiple servers. If you want to accomplish this through GUI it would be quite painful. After digging out I found out an easy way.

1. You can accomplish this by running the below set of query on individual servers.

2. You can accomplish this by running the below set of query on Central Management Server.

For how to configure Central Management Server(CMS) I will add a link soon or may in next post.

Query:

update msdb..sysoperators set email_address = 'YourEmailID@YourDomain' where name='YourAlertName'

GO

update a SET a.recipients='YourEmailID@YourDomain' 

from msdb..sysmail_mailitems a

inner join msdb..sysmail_profile b

on a.profile_id = b.profile_id

where b.description=’YourDescriptionName’