в 13:46, 26 мая 2010

2010-05-26T13:46:16Z

Admin: 1 версия

2010-05-26T10:23:55Z

1 версия

Новая страница

== Managing Application Roles==



<source lang="sql">
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</source>

== Managing Fixed Database Role Membership: sp_addrolemember==



<source lang="sql">
USE YourDatabaseName
GO
EXEC sp_addrolemember "db_datawriter", "YourName"
EXEC sp_addrolemember "db_datareader", "YourName"</source>

== Managing Fixed Database Role Membership: sp_droprolemember==



<source lang="sql">
USE YourDatabaseName
GO
EXEC sp_droprolemember "db_datawriter", "YourName"</source>

== Managing Server Role Members==



<source lang="sql">
CREATE LOGIN Veronica
WITH PASSWORD = "YourPass"
GO
EXEC master..sp_addsrvrolemember "sysadmin"
GO
EXEC master..sp_dropsrvrolemember "sysadmin"
GO</source>

== Managing User-Defined Database Roles: alter role==



<source lang="sql">
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</source>

== Managing User-Defined Database Roles: Create==



<source lang="sql">
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</source>

== Managing User-Defined Database Roles: drop role==



<source lang="sql">
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</source>

== Managing User-Defined Database Roles: grant==



<source lang="sql">
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</source>

== Reporting Fixed Database Roles Information==



<source lang="sql">
EXEC sp_helpdbfixedrole
GO
EXEC sp_helprolemember
GO</source>

== Reporting Fixed Server Role Information==



<source lang="sql">
SELECT name
FROM sys.server_principals
WHERE type_desc = "SERVER_ROLE"
EXEC sp_helpsrvrole
EXEC sp_helpsrvrolemember "sysadmin"</source>

== Some of the SQL Server fixed server roles==



<source lang="sql">
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.</source>

← Предыдущая	Версия 13:46, 26 мая 2010
(нет различий)

SQL Server/T-SQL Tutorial/User Role/Role - История изменений

в 13:46, 26 мая 2010

Admin: 1 версия