Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

grant access to all databases Expand / Collapse
Author
Message
Posted Thursday, November 18, 2010 2:04 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, November 21, 2014 11:37 AM
Points: 208, Visits: 1,037
hello experts,
i have around 600 databases in my server, a user need select access of all the databases. will i have to go one by one in all the dbs and create tht user and give datareader role to him. or is thr any shorter way to do so????

thanks in advance
Post #1022679
Posted Thursday, November 18, 2010 2:19 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, August 1, 2014 6:18 AM
Points: 1,618, Visits: 20,980
Cross post. All responses at http://www.sqlservercentral.com/Forums/Topic1022684-391-1.aspx

Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga
Post #1022690
Posted Thursday, November 18, 2010 5:33 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 9:08 PM
Points: 237, Visits: 902
Hi,

You have to apply below mention script with your all database..

Syntax
USE [DatabaseName]
GO
CREATE USER [UserName] FOR LOGIN [LoginName] WITH DEFAULT_SCHEMA =schema_name
GO
EXEC sp_addrolemember 'db_datareader', 'UserName'

Script For All Databases
---------------------------------------------------------------------
USE master
GO
DECLARE @LoginName varchar(256)
SET @LoginName ='YourLoginName'

SELECT 'USE [' + Name + ']'
+ ';'
+ 'CREATE USER [' + @LoginName + '] FOR LOGIN [' + @LoginName + '] WITH DEFAULT_SCHEMA =dbo'
+ ';'
+ 'EXEC sp_addrolemember ''db_datareader'', '''+ @LoginName + ''''
AS ScriptToExecute

FROM sys.databases
WHERE name NOT IN ('Master','tempdb','model','msdb') -- Avoid System Databases
AND (state_desc ='ONLINE') -- Avoid Offline Databases
AND (source_database_id Is Null) -- Avoid Database Snapshot
ORDER BY Name
---------------------------------------------------------------------

Copy output of this select query and open new query window and then execute..


Ram
MSSQL DBA
Post #1022793
Posted Wednesday, July 4, 2012 9:54 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Today @ 5:29 AM
Points: 4, Visits: 64
Thank you, it was helpfull.

Nira



Post #1325084
Posted Tuesday, July 23, 2013 4:50 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 1:07 PM
Points: 5, Visits: 77
Edit posted
Post #1476830
Posted Tuesday, July 23, 2013 9:46 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 2:43 AM
Points: 1,940, Visits: 2,375
You arw getting this error because you have specified db_datareader, db_datawriter' as a single role and due to this sql server not able to identify it as a valid role..... Specify both roles seperatly and it will work


_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1476867
Posted Wednesday, July 24, 2013 9:21 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 9:37 AM
Points: 290, Visits: 877
600 databases on one instance?? That maintenance schedule must be fun!
Post #1477130
Posted Friday, July 26, 2013 10:18 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 1:07 PM
Points: 5, Visits: 77
Typo my bad, it is 60 Databases...but we do have 200+ on 4 node cluster environment
Post #1478098
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse