How to: Grant Permission on Development Severs as Batch for Windows Authentications

Recently I was asked how to grant permission on Dev servers for team members in SQL Server, actually, there are many way to do that, in this article, I will show you a simple way.

Firstly, you need to save the following sql script as SQL file, here I called it as ‘GrantRightsByUser.sql’

        1: USE [master]

       2: GO

       3: CREATE LOGIN $(login_user) FROM WINDOWS WITH DEFAULT_DATABASE=$(login_db)

       4: GO

       5: USE $(login_db)

       6: GO

       7: CREATE USER $(login_user) FOR LOGIN $(login_user)

       8: GO

       9: EXEC sp_addrolemember N'db_datareader', $(login_user)

      10: EXEC sp_addrolemember N'db_datawriter', $(login_user)

      11: EXEC sp_addrolemember N'db_owner',$(login_user)

      12: GO

Sencondly, you need to create another command file named ‘GrantRightsByUser.cmd’ with scripts below:

       1: start sqlcmd -S dev01 -E -i GrantRightsByUser.sql -vlogin_user="[contoso\Jack]" login_db="[db1]"

       2: start sqlcmd -S dev01 -E -i GrantRightsByUser.sql -vlogin_user="[contoso\Bob]" login_db="[db1]"

       3: start sqlcmd -S dev01 -E -i GrantRightsByUser.sql -vlogin_user="[contoso\John]" login_db="[db1]"

Note: dev01 is a SQL alias that direct to SQL instance, you can use cliconfg command to config sql alias.