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

Chrome 138 the last supported

Chrome 138 the last supported  Hey Everyone, Chrome 138 is the last version of Chrome that will support Android 8.0 (Oreo) and Android 9.0 (...