sql;gutter:true;">用户自定义数据库用户
--声明数据库引用
use database_name;
go
--修改用户自定义数据库用户
alter user user_name
with 
name=new_user_name,default_schema={ schemaname | null },login=login_name,password='password' [old_password='old_password'],default_language={ none |  |  |  },allow_encrypted_value_modifications={ on | off } 
--添加拥有的架构

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

删除拥有的架构（把架构付给自己就行了）

go

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

go

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

go

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

go

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

go

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

go

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

go

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

go

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

go

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

go

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

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

alter role [db_accessadmin] drop member user_name;

go

alter role [db_backupoperator] drop member user_name;

go

alter role [db_datareader] drop member user_name;

go

alter role [db_datawriter] drop member user_name;

go

alter role [db_ddladmin] drop member user_name;

go

alter role [db_denydatareader] drop member user_name;

go

alter role [db_denydatawriter] drop member user_name;

go

alter role [db_owner] drop member user_name;

go

alter role [db_securityadmin] drop 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

--删除扩展注释

exec sys.sp_dropextendedproperty @name=N'description_name',@level0name=N'user_name'

go

sql;gutter:true;">数据库引用
use [testss];
go
--添加拥有的架构

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

go

--删除拥有的架构

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

go
--添加成员身份

alter role [db_backupoperator] add member test1;

go

alter role [db_datareader] add member test1;

go

--删除成员身份

alter role [db_backupoperator] drop member test1;

go

alter role [db_datareader] drop member test1;

go
--安全对象

--授予权限

--备份日志

grant backup log to test1;

go
--扩展属性

--删除扩展属性

exec sys.sp_dropextendedproperty @name=N'tests_description',@level0name=N'test1'

go

--添加扩展注释

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

go
--修改当前数据库用户自定义用户属性

alter user test1

with

name=test1,default_schema=dbo,--login=tests,--password='1234' old_password='1234',--default_language=English,allow_encrypted_value_modifications=off;

go