Dec
23
2011

Script to create SQL login and assign read privileges on master and msdb database for automation purpose

Today got a task to create a common account across all SQL servers which have read privileges on master and msdb database, so to carry out this activity, wrote a small script as follows for SQL 2000 and SQL 2005/2008,

Note: Replace test with your account name and password as your desired password

Script for SQL 2005/2008,


SET NOCOUNT ON
USE
MASTER
CREATE
LOGIN [test] WITH PASSWORD=N'test@123', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

USE MASTER
CREATE
USER [test] WITH DEFAULT_SCHEMA = dbo
exec sp_addrolemember N'db_datareader', N'test'
GO

USE MSDB
CREATE USER [test] WITH DEFAULT_SCHEMA = dbo
exec sp_addrolemember N'db_datareader', N'test'
GO

PRINT ' " '+ CONVERT(VARCHAR(20),SERVERPROPERTY('machinename')) + '"Test account created"'

SET NOCOUNT OFF


Now, to check whther accounts are created or not, use following script,


SET NOCOUNT ON

select serverproperty('servername') as N'Server Name', loginname from master..syslogins where name = 'test'

SET NOCOUNT OFF


Script for SQL 2000 (it differs from above because SQL 2000 requries less parameters than SQL 2005/2008),


SET NOCOUNT ON

USE master;
exec sp_addlogin @loginame = 'test' , @passwd = N'test@123' , @defdb = 'master' , @deflanguage = 'us_english'

USE master;
exec sp_adduser @loginame = 'test', @name_in_db = 'test', @grpname = 'db_datareader'

USE msdb;
exec sp_adduser @loginame = 'test', @name_in_db = 'test', @grpname = 'db_datareader'

PRINT ' "" '+ CONVERT(VARCHAR(20),serverproperty('servername')) + '""test account created""'

SET NOCOUNT OFF


Now you can check whether login created or not using the same script as above,


SET NOCOUNT ON

select serverproperty('servername') as N'Server Name', loginname from master..syslogins where name = 'test'

SET NOCOUNT OFF


P.S: You may encounter few errors, like the one I posted at – link

Hope it helps 🙂

Download PDF

Related Posts

About the Author: Nitin Garg

Indian born, trekker, biker, photographer, lover of monsoons... I love to blog the topics I research and find useful for self or online community to save time and energy :) Everything you read on my blog is my own personal opinion!

Leave a comment

Subscribe to this blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 195 other subscribers

Translate this blog!

EnglishFrenchGermanItalianPortugueseRussianSpanish

Calender

August 2019
M T W T F S S
« Nov    
 1234
567891011
12131415161718
19202122232425
262728293031  

View Post by Categories

%d bloggers like this: