Add Role to User

I create this script because I was tired of forgetting to check to see if a user actually had access to the database before granting a database role to it.  So, it will add them to the server, the database, and the role.  Just run the script in the database where the role exists.

DECLARE @usrName NVARCHAR(100)
DECLARE @roleName NVARCHAR(100)
DECLARE @cSQL NVARCHAR(max)

SET @usrName = '<domain><username>'
SET @roleName = '<database role>'

IF NOT EXISTS (
        SELECT NAME
        FROM sys.server_principals
        WHERE NAME = @usrName
        )
BEGIN
    PRINT 'Creating Login for [' + @usrName + ']'
    SET @cSQL = 'CREATE LOGIN [' + @usrName + '] FROM WINDOWS'
    EXEC sp_executesql @cSQL
END
IF NOT EXISTS (
        SELECT NAME
        FROM sys.database_principals
        WHERE NAME = @usrName
        )
BEGIN
    PRINT 'Creating User for [' + @usrName + ']'
    SET @cSQL = 'CREATE USER [' + @usrName + '] FROM LOGIN [' + @usrName + ']'
    EXEC sp_executesql @cSQL
END
IF IS_ROLEMEMBER(@roleName, @usrName) = 0
BEGIN
    PRINT 'Granting ' + @roleName + ' role for [' + @usrName + ']'
    EXEC sp_addrolemember @roleName
        , @usrName
END
GO
Posted in SQL

Leave a Reply

Fill in your details below or click an icon to log in:

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

Follow SQL Uber Geek on WordPress.com
Try Audible and Get Two Free Audiobooks
%d bloggers like this: