Tuesday, 21 April 2015

ADD Users to SQL via TSQL Script

I had need to create adhoc users with an SQL login so I did some digging and put together this TSQL



GO
declare @username as varchar(50)
set @username ='test1'
DECLARE @SQL NVARCHAR(4000);
begin

SET @SQL = 'CREATE LOGIN ' + @username + ' WITH PASSWORD = ''12345'', DEFAULT_DATABASE=[Logic], DEFAULT_LANGUAGE=[British], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF';
EXECUTE(@SQL);

end

use {Your DB Name}
EXEC sp_grantdbaccess @username
EXEC SP_ADDROLEMEMBER N'db_datareader', @username;
        EXEC SP_ADDROLEMEMBER N'db_datawriter', @username;


The script can be put into a procedure and passing the username to it will create a user with read \right privileges on the DB.

No comments:

Post a Comment

Samsung Android Keyboard

  If you have a Samsung phone or pad and the keyboard is set to Google give the Samsung one a try. Just go into the keyboard settings and ...