Skip to main content

Posts

Showing posts with the label deny role

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