sql;gutter:true;">用户自定义数据库用户
----声明数据库引用
--use database_name;
--go
----windows用户
--create user user_name for login login_name with default_schema=architecture_name,allow_encrypted_value_modifications={ on | off };
----不带登录名的sql用户
--create user user_name without login with default_schema=architecrure_name,allow_encrypted_value_modifications={ on | off };
----带登录名的sql用户
--create user user_name for login login_name with default_schema=architecture_name,allow_encrypted_value_modifications={ on | off };
----映射到非对称密钥的用户
--create user user_name for asymmetric key asym_key_name;
----映射到证书的用户
--create user user_name for certificate certificate_name;
--拥有的架构

--use database_name;

--go

--alter authorization on schema::[db_accessadmin] to user_name;

--go

--alter authorization on schema::[db_backupoperator] to user_name;

--go

--alter authorization on schema::[db_datareader] to user_name;

--go

--alter authorization on schema::[db_datawriter] to user_name;

--go

--alter authorization on schema::[db_ddladmin] to user_name;

--go

--alter authorization on schema::[db_denydatareader] to user_name;

--go

--alter authorization on schema::[db_denydatawriter] to user_name;

--go

--alter authorization on schema::[db_owner] to user_name;

--go

--alter authorization on schema::[db_securityadmin] to user_name;

--go

--alter authorization on schema::[guest] to user_name;

--go
--成员身份

--use database_name;

--go

--alter role [db_accessadmin] add member user_name;

--go

--alter role [db_backupoperator] add member user_name;

--go

--alter role [db_datareader] add member user_name;

--go

--alter role [db_datawriter] add member user_name;

--go

--alter role [db_ddladmin] add member user_name;

--go

--alter role [db_denydatareader] add member user_name;

--go

--alter role [db_denydatawriter] add member user_name;

--go

--alter role [db_owner] add member user_name;

--go

--alter role [db_securityadmin] add member user_name;

--go
----安全对象

----use database_name;

----go

----授予权限

----备份日志

--grant backup log to user_name;

--go

----备份数据库

--grant backup database to user_name;

--go

----插入

--grant insert to user_name;

--go

----查看定义

--grant view definition to user_name;

--go

----查看任意列加密密钥定义

--grant view any column encryption key definition to user_name;

--go

----查看任意列主密钥定义

--grant view any column master key definition to user_name;

--go

----查看数据库状态

--grant view database state to user_name;

--go

----撤销掩码

--grant unmask to user_name;

--go

----创建xml架构集合

--grant create xml schema collection to user_name;

--go

----创建表

--grant create table to user_name;

--go

----创建程序集

--grant create assembly to user_name;

--go

----创建队列

--GRANT CREATE QUEUE to user_name;

--go

----创建对称密钥

--grant create symmetric key to user_name;

--go

----创建非对称密钥

--grant create asymmetric key to user_name;

--go

----创建服务

--grant create service to user_name;

--go

----创建规则

--grant create rule to user_name;

--go

----创建过程

--grant create procedure to user_name;

--go

----创建函数

--grant create function to user_name;

--go

----创建架构

--grant create schema to user_name;

--go

----创建角色

--grant create role to user_name;

--go

----创建类型

--grant create type to user_name;

--go

----创建路由

--grant create route to user_name;

--go

----创建默认值

--grant create default to user_name;

--go

----创建全文目录

--grant create fulltext catalog to user_name;

--go

----创建视图

--grant create view to user_name;

--go

----创建数据库DDL事件通知

--grant create database dll event notification to user_name;

--go

----创建同义词

--grant create synonym to user_name;

--go

----创建消息类型

--grant create message type to user_name;

--go

----创建远程服务绑定

--grant create remote service binding to user_name;

--go

----创建约定

--grant create contract to user_name;

--go

----创建证书

--grant create certificate to user_name;

--go

----订阅查询通知

--grant subscribe query notifications to user_name;

--go

----更改

--grant alter to user_name;

--go

----更改任何外部数据源

--grant alter any external data source to user_name;

--go

----更改任何外部文件格式

--grant alter any external file format to user_name;

--go

----更改任何掩码

--grant alter any mask to user_name;

--go

----更改任意安全策略

--grant alter any security policy to user_name;

--go

----更改任意程序集

--grant alter any assembly to user_name;

--go

----更改任意对称密钥

--grant alter any symmetric key to user_name;

--go

----更改任意非对称密钥

--grant alter any asymmetric key to user_name;

--go

----更改任意服务

--grant alter any service to user_name;

--go

----更改任意架构

--grant alter any schema to user_name;

--go

----更改任意角色

--grant alter any role to user_name;

--go

----更改任意路由

--grant alter any route to user_name;

--go

----更改任意全文目录

--grant alter any fulltext catalog to user_name;

--go

----更改任意数据空间

--grant alter any dataspace to user_name;

--go

----更改任意数据库DDL数据库触发器

--grant alter any database ddl trigger to user_name;

--go

----更改任意数据库审核

--grant alter any database audit to user_name;

--go

----更改任意数据库事件通知

--grant alter any database event notification to user_name;

--go

----更改任意消息类型

--grant alter any message type to user_name;

--go

----更改任意应用程序角色

--grant alter any application role to user_name;

--go

----更改任意用户

--grant alter any user to user_name;

--go

----更改任意远程服务绑定

--grant alter any remote service binding to user_name;

--go

----更改任意约定

--grant alter any contract to user_name;

--go

----更改任意证书

--grant alter any certificate to user_name;

--go

----更新

--grant update to user_name;

--go

----检查点

--grant checkpoint to user_name;

--go

----接管所有权

--grant take ownership to user_name;

--go

----控制

--grant control to user_name;

--go

----控制聚合

--grant create aggregate to user_name;

--go

----连接

--grant connect to user_name;

--go

----连接复制

--grant connect replication to user_name;

--go

----删除

--grant delete to user_name;

--go

----身份验证

--grant authenticate to user_name;

--go

----显示计划

--grant showplan to user_name;

--go

----选择

--grant select to user_name;

--go

----引用

--grant references to user_name;

--go

----执行

--grant execute to user_name;

--go
----授予并允许转售权限

----安全对象

----use database_name;

----go

----备份日志

--grant backup log to user_name with grant option;

--go

----备份数据库

--grant backup database to user_name with grant option;

--go

----插入

--grant insert to user_name with grant option;

--go

----查看定义

--grant view definition to user_name with grant option;

--go

----查看任意列加密密钥定义

--grant view any column encryption key definition to user_name with grant option;

--go

----查看任意列主密钥定义

--grant view any column master key definition to user_name with grant option;

--go

----查看数据库状态

--grant view database state to user_name with grant option;

--go

----撤销掩码

--grant unmask to user_name with grant option;

--go

----创建xml架构集合

--grant create xml schema collection to user_name with grant option;

--go

----创建表

--grant create table to user_name with grant option;

--go

----创建程序集

--grant create assembly to user_name with grant option;

--go

----创建队列

--GRANT CREATE QUEUE to user_name with grant option;

--go

----创建对称密钥

--grant create symmetric key to user_name with grant option;

--go

----创建非对称密钥

--grant create asymmetric key to user_name with grant option;

--go

----创建服务

--grant create service to user_name with grant option;

--go

----创建规则

--grant create rule to user_name with grant option;

--go

----创建过程

--grant create procedure to user_name with grant option;

--go

----创建函数

--grant create function to user_name with grant option;

--go

----创建架构

--grant create schema to user_name with grant option;

--go

----创建角色

--grant create role to user_name with grant option;

--go

----创建类型

--grant create type to user_name with grant option;

--go

----创建路由

--grant create route to user_name with grant option;

--go

----创建默认值

--grant create default to user_name with grant option;

--go

----创建全文目录

--grant create fulltext catalog to user_name with grant option;

--go

----创建视图

--grant create view to user_name with grant option;

--go

----创建数据库DDL事件通知

--grant create database dll event notification to user_name with grant option;

--go

----创建同义词

--grant create synonym to user_name with grant option;

--go

----创建消息类型

--grant create message type to user_name with grant option;

--go

----创建远程服务绑定

--grant create remote service binding to user_name with grant option;

--go

----创建约定

--grant create contract to user_name with grant option;

--go

----创建证书

--grant create certificate to user_name with grant option;

--go

----订阅查询通知

--grant subscribe query notifications to user_name with grant option;

--go

----更改

--grant alter to user_name with grant option;

--go

----更改任何外部数据源

--grant alter any external data source to user_name with grant option;

--go

----更改任何外部文件格式

--grant alter any external file format to user_name with grant option;

--go

----更改任何掩码

--grant alter any mask to user_name with grant option;

--go

----更改任意安全策略

--grant alter any security policy to user_name with grant option;

--go

----更改任意程序集

--grant alter any assembly to user_name with grant option;

--go

----更改任意对称密钥

--grant alter any symmetric key to user_name with grant option;

--go

----更改任意非对称密钥

--grant alter any asymmetric key to user_name with grant option;

--go

----更改任意服务

--grant alter any service to user_name;

--go

----更改任意架构

--grant alter any schema to user_name with grant option;

--go

----更改任意角色

--grant alter any role to user_name with grant option;

--go

----更改任意路由

--grant alter any route to user_name with grant option;

--go

----更改任意全文目录

--grant alter any fulltext catalog to user_name with grant option;

--go

----更改任意数据空间

--grant alter any dataspace to user_name with grant option;

--go

----更改任意数据库DDL数据库触发器

--grant alter any database ddl trigger to user_name with grant option;

--go

----更改任意数据库审核

--grant alter any database audit to user_name with grant option;

--go

----更改任意数据库事件通知

--grant alter any database event notification to user_name with grant option;

--go

----更改任意消息类型

--grant alter any message type to user_name with grant option;

--go

----更改任意应用程序角色

--grant alter any application role to user_name with grant option;

--go

----更改任意用户

--grant alter any user to user_name with grant option;

--go

----更改任意远程服务绑定

--grant alter any remote service binding to user_name with grant option;

--go

----更改任意约定

--grant alter any contract to user_name with grant option;

--go

----更改任意证书

--grant alter any certificate to user_name with grant option;

--go

----更新

--grant update to user_name with grant option;

--go

----检查点

--grant checkpoint to user_name with grant option;

--go

----接管所有权

--grant take ownership to user_name with grant option;

--go

----控制

--grant control to user_name with grant option;

--go

----控制聚合

--grant create aggregate to user_name with grant option;

--go

----连接

--grant connect to user_name with grant option;

--go

----连接复制

--grant connect replication to user_name with grant option;

--go

----删除

--grant delete to user_name with grant option;

--go

----身份验证

--grant authenticate to user_name with grant option;

--go

----显示计划

--grant showplan to user_name with grant option;

--go

----选择

--grant select to user_name with grant option;

--go

----引用

--grant references to user_name with grant option;

--go

----执行

--grant execute to user_name with grant option;

--go
----拒绝权限

----安全对象

--use database_name;

--go

----备份日志

--deny backup log to user_name;

--go

----备份数据库

--deny backup database to user_name;

--go

----插入

--deny insert to user_name;

--go

----查看定义

--deny view definition to user_name;

--go

----查看任意列加密密钥定义

--deny view any column encryption key definition to user_name;

--go

----查看任意列主密钥定义

--deny view any column master key definition to user_name;

--go

----查看数据库状态

--deny view database state to user_name;

--go

----撤销掩码

--deny unmask to user_name;

--go

----创建xml架构集合

--deny create xml schema collection to user_name;

--go

----创建表

--deny create table to user_name;

--go

----创建程序集

--deny create assembly to user_name;

--go

----创建队列

--deny CREATE QUEUE to user_name;

--go

----创建对称密钥

--deny create symmetric key to user_name;

--go

----创建非对称密钥

--deny create asymmetric key to user_name;

--go

----创建服务

--deny create service to user_name;

--go

----创建规则

--deny create rule to user_name;

--go

----创建过程

--deny create procedure to user_name;

--go

----创建函数

--deny create function to user_name;

--go

----创建架构

--deny create schema to user_name;

--go

----创建角色

--deny create role to user_name;

--go

----创建类型

--deny create type to user_name;

--go

----创建路由

--deny create route to user_name;

--go

----创建默认值

--deny create default to user_name;

--go

----创建全文目录

--deny create fulltext catalog to user_name;

--go

----创建视图

--deny create view to user_name;

--go

----创建数据库DDL事件通知

--deny create database dll event notification to user_name;

--go

----创建同义词

--deny create synonym to user_name;

--go

----创建消息类型

--deny create message type to user_name;

--go

----创建远程服务绑定

--deny create remote service binding to user_name;

--go

----创建约定

--deny create contract to user_name;

--go

----创建证书

--deny create certificate to user_name;

--go

----订阅查询通知

--deny subscribe query notifications to user_name;

--go

----更改

--deny alter to user_name;

--go

----更改任何外部数据源

--deny alter any external data source to user_name;

--go

----更改任何外部文件格式

--deny alter any external file format to user_name;

--go

----更改任何掩码

--deny alter any mask to user_name;

--go

----更改任意安全策略

--deny alter any security policy to user_name;

--go

----更改任意程序集

--deny alter any assembly to user_name;

--go

----更改任意对称密钥

--deny alter any symmetric key to user_name;

--go

----更改任意非对称密钥

--deny alter any asymmetric key to user_name;

--go

----更改任意服务

--deny alter any service to user_name;

--go

----更改任意架构

--deny alter any schema to user_name;

--go

----更改任意角色

--deny alter any role to user_name;

--go

----更改任意路由

--deny alter any route to user_name;

--go

----更改任意全文目录

--deny alter any fulltext catalog to user_name;

--go

----更改任意数据空间

--deny alter any dataspace to user_name;

--go

----更改任意数据库DDL数据库触发器

--deny alter any database ddl trigger to user_name;

--go

----更改任意数据库审核

--deny alter any database audit to user_name;

--go

----更改任意数据库事件通知

--deny alter any database event notification to user_name;

--go

----更改任意消息类型

--deny alter any message type to user_name;

--go

----更改任意应用程序角色

--deny alter any application role to user_name;

--go

----更改任意用户

--deny alter any user to user_name;

--go

----更改任意远程服务绑定

--deny alter any remote service binding to user_name;

--go

----更改任意约定

--deny alter any contract to user_name;

--go

----更改任意证书

--deny alter any certificate to user_name;

--go

----更新

--deny update to user_name;

--go

----检查点

--deny checkpoint to user_name;

--go

----接管所有权

--deny take ownership to user_name;

--go

----控制

--deny control to user_name;

--go

----控制聚合

--deny create aggregate to user_name;

--go

----连接

--deny connect to user_name;

--go

----连接复制

--deny connect replication to user_name;

--go

----删除

--deny delete to user_name;

--go

----身份验证

--deny authenticate to user_name;

--go

----显示计划

--deny showplan to user_name;

--go

----选择

--deny select to user_name;

--go

----引用

--deny references to user_name;

--go

----执行

--deny execute to user_name;

--go
----扩展属性

----声明数据库引用

----use database_name

--go

----添加扩展注释

--exec sys.sp_addextendedproperty   @name=N'description_name',@value=N'description_value',@level0type=N'user',@level0name=N'user_name';

--go

sql;gutter:true;">数据库引用
use [testss];
go
--判断用户是否存在，如果存在则删除，不存在则创建
if exists(select * from sys.database_principals where name='tests')
--把架构修改回来架构自身
alter authorization on schema::[db_accessadmin] to db_accessadmin;
--删除角色拥有的成员
alter role [db_accessadmin] drop member tests;
--删除用户
drop user tests;
go
--创建当前数据库用户自定义用户
create user tests 
for login tests 
with default_schema=dbo,allow_encrypted_value_modifications=on;
--拥有的架构

use testss;

go

alter authorization on schema::[db_accessadmin] to tests;

go
--成员身份

use testss;

go

alter role [db_accessadmin] add member tests;

go
--安全对象

use testss;

go

--授予权限

--备份日志

grant backup log to tests;

go
--扩展属性

--声明数据库引用

--use database_name

go

--添加扩展注释

exec sys.sp_addextendedproperty @name=N'tests_description',@value=N'用户自定义用户描述',@level0name=N'tests';

go