|
Create login
|
USE [master]
GO
CREATE LOGIN [ScmUser] WITH PASSWORD=N'Password1', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
|
|
Create user in [model] database
|
USE [model]
GO
CREATE USER [ScmUser] FOR LOGIN [ScmUser]
GO
|
| Create user in [msdb] database |
USE [msdb]
GO
CREATE USER [ScmUser] FOR LOGIN [ScmUser]
GO
|
| Create user in [master] database |
USE [master]
GO
CREATE USER [ScmUser] FOR LOGIN [ScmUser]
GO
|
| Grant execute permission to mail procedures |
USE [msdb]
GO
GRANT EXECUTE ON [dbo].[sysmail_help_configure_sp] TO [ScmUser]
GO
GRANT EXECUTE ON [dbo].[sysmail_help_account_sp] TO [ScmUser]
GO
GRANT EXECUTE ON [dbo].[sysmail_help_profile_sp] TO [ScmUser]
GO
GRANT EXECUTE ON [dbo].[sysmail_help_profileaccount_sp] TO [ScmUser]
GO
GRANT EXECUTE ON [dbo].[sysmail_help_principalprofile_sp] TO [ScmUser]
GO
|
| Grant 'select' permission to 'sysjobs' table |
USE [msdb]
GO
GRANT SELECT ON [dbo].[sysjobs] TO [ScmUser]
GO
|
| Grant 'select' permission to 'sysssispackages' table |
USE [msdb]
GO
GRANT SELECT ON [dbo].[sysssispackages] TO [ScmUser]
GO
|
| Grant 'select' permission to 'log_shipping_primary_databases' table |
USE [msdb]
GO
GRANT SELECT ON [dbo].[log_shipping_primary_databases] TO [ScmUser]
GO
|
| Grant 'select' permission to 'database_mirroring_witnesses' table |
USE [master]
GO
GRANT SELECT ON [sys.][database_mirroring_witnesses] TO [ScmUser]
GO
|
| Grant 'view server state' permission |
USE [master]
GO
GRANT VIEW SERVER STATE TO [ScmUser]
GO
|
| Add login to 'securityadmin' role |
USE [master]
GO
ALTER SERVER ROLE [securityadmin] ADD MEMBER [ScmUser]
GO
|
| Remove login |
USE [master]
GO
IF EXISTS(SELECT * FROM sys.database_principals WHERE name = N'ScmUser')
DROP USER [ScmUser]
GO
USE [model]
GO
IF EXISTS(SELECT * FROM sys.database_principals WHERE name = N'ScmUser')
DROP USER [ScmUser]
GO
USE [msdb]
GO
IF EXISTS(SELECT * FROM sys.database_principals WHERE name = N'ScmUser')
DROP USER [ScmUser]
GO
USE [master]
GO
IF EXISTS (SELECT * FROM master.dbo.syslogins WHERE loginname = N'ScmUser')
DROP LOGIN [ScmUser]
GO
|
