TSQL Add/Delete User, Login, Role (Code sample)

The first group of code uses the system stored procedures that are still currently supported but will soon be deprecated in future updates. The structure of the following codes (1) try to drop the existing login/role/user; (2) create a new login/role/user; (3) grant permissions only for the first three tables in the current database;

Remember that new roles have no permissions granted on them by default. This assures that they would not be able to access certain tables, views, or stored procedures unless specifically specified.

Old, soon to be deprecated code:

DECLARE @Role varchar(20) = 'RestrictedRole'
DECLARE @DBName varchar(20) = 'DatabaseName'
DECLARE @Login varchar(120) = 'RestrictedLogin'
DECLARE @LoginPword varchar(120) = 'pazzword123'
DECLARE @counter int;
DECLARE @tablename varchar(50);
DECLARE @grantStatement nvarchar(200);

BEGIN TRY exec sp_droplogin @login; END TRY
BEGIN CATCH END CATCH

BEGIN TRY exec sp_dropuser @login; END TRY
BEGIN CATCH END CATCH

BEGIN TRY exec sp_droprole @role; END TRY
BEGIN CATCH END CATCH;

EXEC sp_addlogin @loginame = @Login
,@passwd = @LoginPword
,@defdb = @DBName;

EXEC sp_addrole @Role, 'dbo';

EXEC sp_adduser @loginame = @Login
,@grpname = @Role;

Better/more up to date code uses CREATE LOGIN instead of sys.sp_addlogin

DECLARE @Role varchar(20) = 'RestrictedRole'
DECLARE @DBName varchar(20) = 'DatabaseName'
DECLARE @Login varchar(120) = 'RestrictedLogin'
DECLARE @LoginPword varchar(120) = 'pazzword123'
DECLARE @counter int;
DECLARE @tablename varchar(50);
DECLARE @grantStatement nvarchar(200);

BEGIN TRY
SET @grantStatement = 'DROP LOGIN ' + @Login
EXEC dbo.sp_executesql @statement=@grantStatement;
END TRY
BEGIN CATCH END CATCH

BEGIN TRY
SET @grantStatement = 'DROP USER ' + @Login
EXEC dbo.sp_executesql @statement=@grantStatement;
END TRY
BEGIN CATCH END CATCH

BEGIN TRY
SET @grantStatement = 'DROP ROLE ' + @Role
EXEC dbo.sp_executesql @statement=@grantStatement;
END TRY
BEGIN CATCH END CATCH

SET @grantStatement = 'CREATE LOGIN ' + @Login + ' WITH PASSWORD = ''' + @LoginPword + ''', DEFAULT_DATABASE=' + @DBName
EXEC dbo.sp_executesql @statement=@grantStatement;
SET @grantStatement = 'CREATE ROLE ' + @Role
EXEC dbo.sp_executesql @statement=@grantStatement;
SET @grantStatement = 'CREATE USER ' + @Login + ' FOR LOGIN ' + @Login
EXEC dbo.sp_executesql @statement=@grantStatement;

Then to dynamically grant SELECT, INSERT, UPDATE, DELETE permissions on the first three tables in the current database, then append this code on the end of your choice of login/user/role creation code from above (to reuse the declared variables).

CREATE TABLE #SelectInsertUpdateTable
(
ID int IDENTITY(1,1) PRIMARY KEY
,TableName varchar(50)
)

INSERT #SelectInsertUpdateTable (TableName)
select top 3 @DBName + '.dbo.' + sys.tables.NAME from sys.tables

SET @counter = (SELECT COUNT(*) FROM #SelectInsertUpdateTable);
WHILE @counter > 0
BEGIN
SET @tablename = (SELECT TableName FROM #SelectInsertUpdateTable WHERE ID=@counter);
SET @grantStatement = 'GRANT SELECT, INSERT, UPDATE on ' + @tablename + ' to ' + @Login
EXEC dbo.sp_executesql @statement=@grantStatement;
SET @counter-=1;
END

DROP TABLE #SelectInsertUpdateTable;