October 26, 2014

Script to apply any user permission to the all databases of SQL

/* It is a new feature and it has ability to create user defined server roles
and assign server level/scope permissions. */
/*
- Following actions are performed to implement this new feature:
- Created Server role
- Created Login and made member of server role
- Granted Standard view permission
After completion above steps the login has ability to make connection any database with data reader
permission.
*/

USE master
GO
CREATE SERVER ROLE ALLDBREADER
Go
CREATE LOGIN [DBREADER] WITH PASSWORD=N'123',
DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english],
CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
ALTER SERVER ROLE ALLDBREADER ADD MEMBER [DBREADER]
GO
GRANT CONNECT SQL TO ALLDBREADER
GRANT VIEW ANY DATABASE TO ALLDBREADER
GRANT VIEW ANY DEFINITION TO ALLDBREADER
GRANT VIEW SERVER STATE to ALLDBREADER
GRANT CONTROL SERVER TO ALLDBREADER
DENY SHUTDOWN TO ALLDBREADER
Go

USE master
GO
CREATE SERVER ROLE ALLDBREADER
Go
GRANT CONTROL SERVER TO ALLDBREADER
DENY ALTER ANY DATABASE TO ALLDBREADER
Go