November 12, 2008 at 12:47 pm
Hi, here is my problem. I need to add a user to my server. Here is the script.
CREATE LOGIN ABC WITH PASSWORD = 'ABC#123',
DEFAULT_DATABASE = XYZ,
CHECK_EXPIRATION = OFF, CHECK_POLICY = OFF
I am done with this.
Now I need to give read only access to this login to ALL the databases except master, model and msdb.
I tried pointing from the drop down menu for the particular database (for example selected database XYZ) and then ran this script:
EXEC sp_grantdbaccess 'ABC', 'ABC#123'
go
EXEC sp_addrolemember 'db_datareader', 'ABC'
go
It works fine. But I have 100 over databases and like 20 servers. I want to just run one script that can do it on all databases rather than selecting each databases from the drop down menu.
Anyone can help me with that script?
[font="Verdana"]Imagination is more important than knowledge-Albert Einstein[/font]
November 12, 2008 at 1:49 pm
Nevermind, I got it myself...
I will post the complete query...perhaps will help someone who has similar issue.
Run this first:
CREATE LOGIN ABC WITH PASSWORD = 'ABC#123',
DEFAULT_DATABASE = XYZ,
CHECK_EXPIRATION = OFF, CHECK_POLICY = OFF
Then Run this:
DECLARE @command varchar(1000)
SELECT @command = 'IF ''?'' NOT IN(''master'', ''model'', ''msdb'', ''tempdb'') BEGIN USE ?
EXEC(''sp_grantdbaccess "ABC", "ABC#123"'')
EXEC(''sp_addrolemember "db_datareader", "ABC"'')END'
EXEC sp_MSforeachdb @command
go
:):D:w00t:
[font="Verdana"]Imagination is more important than knowledge-Albert Einstein[/font]
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply