SQL Server/T-SQL Tutorial/User Role/Role

Managing Application Roles

CREATE APPLICATION ROLE DataWareHouseApp
WITH PASSWORD = "YourPass",
DEFAULT_SCHEMA = dbo
-- Now grant this application role permissions
GRANT SELECT ON YourTable1
TO DataWareHouseApp
EXEC sp_setapprole "DataWareHouseApp", -- App role name
"YourPass" -- Password
-- Works
SELECT COUNT(*) FROM YourTable1
ALTER APPLICATION ROLE DataWareHouseApp
WITH NAME = DW_App, PASSWORD = "YourPass2"
DROP APPLICATION ROLE DW_App

Managing Fixed Database Role Membership: sp_addrolemember

USE YourDatabaseName
GO
EXEC sp_addrolemember "db_datawriter", "YourName"
EXEC sp_addrolemember "db_datareader", "YourName"

Managing Fixed Database Role Membership: sp_droprolemember

USE YourDatabaseName
GO
EXEC sp_droprolemember "db_datawriter", "YourName"

Managing Server Role Members

CREATE LOGIN Veronica
WITH PASSWORD = "YourPass"
GO
EXEC master..sp_addsrvrolemember "sysadmin"
GO
EXEC master..sp_dropsrvrolemember "sysadmin"
GO

Managing User-Defined Database Roles: alter role

USE YourDatabaseName
GO
CREATE ROLE YourRoleName AUTHORIZATION db_owner
GRANT SELECT ON YourTableName TO YourRoleName
EXEC sp_addrolemember "YourRoleName", "YourName"
GO
ALTER ROLE YourRoleName WITH NAME = YourRoleNameNew
DROP ROLE YourRoleNameNew
EXEC sp_droprolemember "YourRoleNameNew",
"YourName"
GO
DROP ROLE YourRoleNameNew

Managing User-Defined Database Roles: Create

USE YourDatabaseName
GO
CREATE ROLE YourRoleName AUTHORIZATION db_owner
GRANT SELECT ON YourTableName TO YourRoleName
EXEC sp_addrolemember "YourRoleName", "YourName"
GO
ALTER ROLE YourRoleName WITH NAME = YourRoleNameNew
DROP ROLE YourRoleNameNew
EXEC sp_droprolemember "YourRoleNameNew", "YourName"
GO
DROP ROLE YourRoleNameNew

Managing User-Defined Database Roles: drop role

USE YourDatabaseName
GO
CREATE ROLE YourRoleName AUTHORIZATION db_owner
GRANT SELECT ON YourTableName TO YourRoleName
EXEC sp_addrolemember "YourRoleName",
"YourName"
GO
ALTER ROLE YourRoleName WITH NAME = YourRoleNameNew
DROP ROLE YourRoleNameNew
EXEC sp_droprolemember "YourRoleNameNew",
"YourName"
GO
DROP ROLE YourRoleNameNew

Managing User-Defined Database Roles: grant

USE YourDatabaseName
GO
CREATE ROLE YourRoleName AUTHORIZATION db_owner
GRANT SELECT ON YourTableName TO YourRoleName
EXEC sp_addrolemember "YourRoleName", "YourName"
GO
ALTER ROLE YourRoleName WITH NAME = YourRoleNameNew
DROP ROLE YourRoleNameNew
EXEC sp_droprolemember "YourRoleNameNew", "YourName"
GO
DROP ROLE YourRoleNameNew

Reporting Fixed Database Roles Information

EXEC sp_helpdbfixedrole
GO
EXEC sp_helprolemember
GO

Reporting Fixed Server Role Information

SELECT name
FROM sys.server_principals
WHERE type_desc = "SERVER_ROLE"
EXEC sp_helpsrvrole
EXEC sp_helpsrvrolemember "sysadmin"

Some of the SQL Server fixed server roles

Role            Description
sysadmin        Can perform any activity on the server.
securityadmin   Can manage login IDs and passwords for the server.
dbcreator       Can create, alter, and drop databases.