SQL Server Objects Permissions Explore

Objective: To explore SQL Server Object Permission:

DATABASE PERMISSIONS FOR USER:

USE [DB NAME]
GO
EXEC sp_addrolemember N'db_accessadmin', N'USERNAME'

EXEC sp_addrolemember N'db_ddladmin', N'USERNAME'
GO
USE [DB NAME]
GO
EXEC sp_addrolemember N'db_backupoperator', N'USERNAME'
GO
USE [DB NAME]
GO
EXEC sp_addrolemember N'db_denydatareader', N'USERNAME'
GO
USE [DB NAME]
GO
EXEC sp_addrolemember N'db_denydatawriter', N'USERNAME'
GO
USE [DB NAME]
GO
EXEC sp_addrolemember N'db_securityadmin', N'USERNAME'
GO

STORED PROCEDURE PERMISSION:

GRANT ALTER ON [dbo].[SP_NAME] TO [USER NAME]
GO
GRANT CONTROL ON [dbo].[SP_NAME] TO [USER NAME]
GO
GRANT TAKE OWNERSHIP ON [dbo].[SP_NAME] TO [USER NAME]
GO
GRANT EXECUTE ON [dbo].[SP_NAME] TO ANSON
GO
GRANT VIEW DEFINITION ON [dbo].[SP_NAME] TO [USER NAME]
GO
GRANT EXECUTE ON employee_proc TO public

TABLE LEVEL PERMISSIONS:

GRANT SELECT ON TableName TO Alex
DENY SELECT ON TableName TO Alex
REVOKE SELECT ON TableName TO Alex
DENY SELECT ON TableName TO Sales, Tech - HERE WE CAN DENY FOR TWO ROLES
DENY UPDATE, DELETE, INSERT ON TableName TO Accounting

Monitoring Tool for SQL Server

Objective: To explore the monitoring Tools for SQL Server.

Please use the below link to explore yourself in monitoring.

SQL Server Monitoring Tool Reviews

Object Permissions in SQL Server

Objective: To explore certain syntax for giving permissions in SQL Server Objects:

DATABASE PERMISSIONS FOR USER:

USE [DB NAME]

GO

EXEC sp_addrolemember N'db_accessadmin', N'USERNAME'

GO

USE [DB NAME]

GO

EXEC sp_addrolemember N'db_backupoperator', N'USERNAME'

GO

USE [DB NAME]

GO

EXEC sp_addrolemember N'db_denydatareader', N'USERNAME'

GO

USE [DB NAME]

GO

EXEC sp_addrolemember N'db_denydatawriter', N'USERNAME'

GO

USE [DB NAME]

GO

EXEC sp_addrolemember N'db_securityadmin', N'USERNAME'

GO

STORED PROCEDURE PERMISSION:

GRANT ALTER ON [dbo].[SP_NAME] TO [USER NAME]

GO

GRANT CONTROL ON [dbo].[SP_NAME] TO [USER NAME]

GO

GRANT TAKE OWNERSHIP ON [dbo].[SP_NAME] TO [USER NAME]

GO

GRANT EXECUTE ON [dbo].[SP_NAME] TO ANSON

GO

GRANT VIEW DEFINITION ON [dbo].[SP_NAME] TO [USER NAME]

GO

GRANT EXECUTE ON employee_proc TO public

TABLE LEVEL PERMISSIONS:

GRANT SELECT ON TableName TO Alex

DENY SELECT ON TableName TO Alex

REVOKE SELECT ON TableName TO Alex

DENY SELECT ON TableName TO Sales, Tech - HERE WE CAN DENY FOR TWO ROLES

DENY UPDATE, DELETE, INSERT ON TableName TO Accounting

Also refer the below articles:

1. Database Journal

2. searchsqlserver.techtarget.com

3. MSSQL Tips

Detach and Attach DB

How to Detach and Attach DB:

1. Location of files are in : C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\

2. Our purpose is to move the database from C: Drive to D: Drive

3. Execute the below Procedure:

USE [master]

GO

EXEC master.dbo.sp_detach_db @dbname = N'Reporting', @keepfulltextindexfile=N'true'

4. Move the mdf and ldf files to the D: Drive Now:

5. Then Execute the below procedure to attach db:

EXEC sp_attach_db @dbname = 'Reporting',

@filename1 = 'D:\data\Reporting\Reporting.mdf',

@filename2 = 'D:\data\Reporting\Reporting_log.ldf'