May 26, 2011 at 2:53 pm
Is there an easy way to give read only (data and definitions) to all databases on a server?
May 26, 2011 at 3:02 pm
not an easy way...
every Login would need a user in each databaser, and each user would need to be added to a role that gives them read access and VIEW ANY DEFINITION...and maybe denies their write access as well.
a little easier if everyone we are talking about is in a specific windows group, then you can add the windows role to a readonly role each database as described above
you could script something that adds the to all current databases, but new databases or restores would break the accessability...
from there you'd need to use an event notification(which i have not got to work yet) to run code after an event like restoring a database or adding a database occurs (if you want it auto-and instantaneous)
or you could schedule a job that peeks at each database, say , once a day andd adds as needed.
Lowell
May 26, 2011 at 3:25 pm
It would only be for a single windows group.
May 26, 2011 at 3:28 pm
this is the command I was playing around with:
EXECUTE master.sys.sp_MSforeachdb 'USE [?]; exec sp_addrolemember ''db_datareader'',''Domain\MyNTGroup'' '
May 26, 2011 at 3:48 pm
Tom Van Harpen (5/26/2011)
this is the command I was playing around with:
EXECUTE master.sys.sp_MSforeachdb 'USE [?]; exec sp_addrolemember ''db_datareader'',''Domain\MyNTGroup'' '
Tom the way i've done it is like this: you'd just have to change the group if it's not the same, and format the quotes to fit inside the sp_msForEachDB:
CREATE USER [NT AUTHORITY\Authenticated Users] FOR LOGIN [NT AUTHORITY\Authenticated Users];
EXEC sp_addrolemember N'db_datareader', N'NT AUTHORITY\Authenticated Users';
GRANT VIEW ANY DEFINITION TO [NT AUTHORITY\Authenticated Users] ;
EXEC sp_msForEachDB
'CREATE USER [NT AUTHORITY\Authenticated Users] FOR LOGIN [NT AUTHORITY\Authenticated Users];
EXEC sp_addrolemember N''db_datareader'', N''NT AUTHORITY\Authenticated Users'';
GRANT VIEW ANY DEFINITION TO [NT AUTHORITY\Authenticated Users] ;'
Lowell
May 27, 2011 at 1:33 pm
this is what I ended up with:
Note: the GRANT VIEW ANY DEFINITION appeared to allow the user to change a stored procedure.
From http://msdn.microsoft.com/en-us/library/ms175808.aspx
Server Scope
VIEW ANY DEFINITION permission granted at this scope effectively negates permissions-based metadata access for the grantee. This means that the grantee can see all metadata in the instance of SQL Server unless the grantee is denied VIEW DEFINITION or CONTROL permissions at the database scope, schema scope, or for an individual entity such as a table. For information about the syntax to use for this permission at this scope, see GRANT (Transact-SQL).
Not sure what "Control" means in this case but I found that "GRANT VIEW ANY DATABASE" prevented the user from making changes.
This is the script I used:
-- ADD USERS
-- add the user to each database
EXECUTE master.sys.sp_MSforeachdb 'USE [?]; IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N''Domain\NT User'')
BEGIN
CREATE USER [Domain\NT User] FOR LOGIN [Domain\NT User]
PRINT ''User Created in '' + DB_NAME()
END
ELSE
PRINT ''User Already Existed in '' + DB_NAME() '
go
-- add the user to each database db_datareader role
EXECUTE master.sys.sp_MSforeachdb 'USE [?]; IF EXISTS (SELECT * FROM sys.database_principals WHERE name = N''Domain\NT User'')
BEGIN
exec sp_addrolemember ''db_datareader'',''Domain\NT User''
PRINT ''User added to role in '' + DB_NAME()
END
ELSE
PRINT ''User does not exist in '' + DB_NAME() '
go
-- grant view any definition to user
USE master
go
GRANT VIEW ANY DATABASE TO [Domain\NT User]
PRINT 'Server wide permission granted'
/*
-- DROP USERS
EXECUTE master.sys.sp_MSforeachdb 'USE [?]; IF EXISTS (SELECT * FROM sys.database_principals WHERE name = N''Domain\NT User'')
BEGIN
DROP USER [Domain\NT User];
PRINT ''User dropped from '' + DB_NAME()
END
Else
PRINT ''User did not exist in '' + DB_NAME()
*/
December 19, 2011 at 12:50 pm
one more variation:
EXECUTE master.sys.sp_MSforeachdb 'USE [?]; IF DB_Name() NOT IN (''Master'', ''Tempdb'', ''model'', ''msdb'' ,''distribution'')
BEGIN
PRINT ''Database: '' + DB_NAME()
EXEC sp_changedbowner ''sa''
END
'
go
Dan Pitta
December 19, 2011 at 1:07 pm
You're probably already aware of this, but ff you're constantly adding new databases to this server, just add the credentials to the model database as well and any new database created will automatically have the permissions generated when the new database is created.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply