SQLServer之修改用户自定义数据库用户

修改用户自定义数据库用户注意事项默认架构将是服务器为此数据库用户解析对象名时将搜索的第一个架构。 除非另外指定，否则默认架构将是此数据库用户创建的对象所属的架构。如果用户具有默认架构，则将使用默认架构。 如果用户不具有默认架构，但该用户是具有默认架构的组的成员，则将使用该组的默认架构。 如果用户不具有默认架构而且是多个组的成员，则该用户的默认架构将是具有最低 principle_id 的 Windows 组的架构和一个显式设置的默认架构。 如果不能为用户确定默认架构，则将使用 dbo 架构。可以将 DEFAULT_SCHEMA 设置为数据库中当前不存在的架构。 因此，可以在创建架构之前将 DEFAULT_SCHEMA 分配给用户。不能为映射到证书或非对称密钥的用户指定 DEFAULT_SCHEMA。如果用户是 sysadmin 固定服务器角色的成员，则忽略 DEFAULT_SCHEMA 的值。 sysadmin 固定服务器角色的所有成员都有默认架构 dbo。仅当新用户名的 SID 与在数据库中记录的 SID 匹配时，才能更改映射到 Windows 登录名或组的用户的名称。此检查将帮助防止数据库中的 Windows 登录名欺骗。使用 WITH LOGIN 子句可以将用户重新映射到一个不同的登录名。 不能使用此子句重新映射以下用户：不具有登录名的用户、映射到证书的用户或映射到非对称密钥的用户。 只能重新映射 SQL 用户和 Windows 用户（或组）。 不能使用 WITH LOGIN 子句更改用户类型，例如将 Windows 帐户更改为 SQL Server 登录名。如果满足以下条件，则用户的名称会自动重命名为登录名。用户是一个 Windows 用户。名称是一个 Windows 名称（包含反斜杠）。未指定新名称。当前名称不同于登录名。如果不满足上述条件，则不会重命名用户，除非调用方另外调用了 NAME 子句。被映射到 SQL Server 登录名、证书或非对称密钥的用户名不能包含反斜杠字符 ()。更改用户名需要具有 ALTER ANY USER 权限。更改用户的目标登录名需要对数据库拥有 CONTROL 权限。若要更改对数据库拥有 CONTROL 权限的用户名名称，则需要对数据库拥有 CONTROL 权限。更改默认架构或语言需要对用户拥有 ALTER 权限。 用户可更改自己的默认架构或语言。使用SSMS数据库管理工具修改用户自定义数据库用户1、连接服务器-》在对象资源管理器窗口-》展开数据库-》选择数据库并展开-》展开安全性-》展开用户-》选择要修改的用户右键点击-》选择属性。2、在数据库用户弹出框-》点击常规-》修改用户默认架构。3、在数据库用户弹出框-》点击拥有的架构-》添加此用户拥有的架构。4、在数据库用户弹出框-》点击成员身份-》添加或者删除数据库角色成员身份。5、在数据库用户弹出框-》点击安全对象-》点击搜索添加安全对象-》点击安全对象修改安全对象拥有的权限。6、在数据库用户弹出框-》点击扩展属性-》添加或者删除扩展属性。使用T-SQL脚本修改用户自定义数据库用户语法–创建用户自定义数据库用户–声明数据库引用use database_name;go–修改用户自定义数据库用户alter user user_namewithname=new_user_name,default_schema={ schemaname | null },login=login_name,password=‘password’ [old_password=‘old_password’],default_language={ none | <lcid> | <language_name> | <language alias> },allow_encrypted_value_modifications={ on | off } –添加拥有的架构use database_name;goalter authorization on schema::[db_accessadmin] to user_name;goalter authorization on schema::[db_backupoperator] to user_name;goalter authorization on schema::[db_datareader] to user_name;goalter authorization on schema::[db_datawriter] to user_name;goalter authorization on schema::[db_ddladmin] to user_name;goalter authorization on schema::[db_denydatareader] to user_name;goalter authorization on schema::[db_denydatawriter] to user_name;goalter authorization on schema::[db_owner] to user_name;goalter authorization on schema::[db_securityadmin] to user_name;goalter authorization on schema::[guest] to user_name;go删除拥有的架构（把架构付给自己就行了）goalter authorization on schema::[db_accessadmin] to db_accessadmin;goalter authorization on schema::[db_backupoperator] to db_backupoperator;goalter authorization on schema::[db_datareader] to db_datareader;goalter authorization on schema::[db_datawriter] to db_datawriter;goalter authorization on schema::[db_ddladmin] to db_ddladmin;goalter authorization on schema::[db_denydatareader] to db_denydatareader;goalter authorization on schema::[db_denydatawriter] to db_denydatawriter;goalter authorization on schema::[db_owner] to db_owner;goalter authorization on schema::[db_securityadmin] to db_securityadmin;goalter authorization on schema::[guest] to guest;go –添加成员身份use database_name;goalter role [db_accessadmin] add member user_name;goalter role [db_backupoperator] add member user_name;goalter role [db_datareader] add member user_name;goalter role [db_datawriter] add member user_name;goalter role [db_ddladmin] add member user_name;goalter role [db_denydatareader] add member user_name;goalter role [db_denydatawriter] add member user_name;goalter role [db_owner] add member user_name;goalter role [db_securityadmin] add member user_name;go–删除成员身份use database_name;goalter role [db_accessadmin] drop member user_name;goalter role [db_backupoperator] drop member user_name;goalter role [db_datareader] drop member user_name;goalter role [db_datawriter] drop member user_name;goalter role [db_ddladmin] drop member user_name;goalter role [db_denydatareader] drop member user_name;goalter role [db_denydatawriter] drop member user_name;goalter role [db_owner] drop member user_name;goalter 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_namego–添加扩展注释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’, @level0type=N’user’,@level0name=N’user_name’go语法注释–database_name–数据库名称–user_name–指定在此数据库中用于识别该用户的名称。–login=login_name–通过将用户的安全标识符(SID)更改为另一个登录名的SID，使用户重新映射到该登录名。–如果ALTER USER语句是SQL批处理中唯一的语句，则Windows Azure SQL Database将支持WITH LOGIN子句。 –如果 ALTER USER 语句不是SQL批处理中唯一的语句或在动态SQL中执行，则不支持WITH LOGIN子句。–name=new_user_name–指定此用户的新名称。 newUserName 不能已存在于当前数据库中。–default_schema={ schemaname | null }–指定服务器在解析此用户的对象名时将搜索的第一个架构。 –将默认架构设置为NULL将从Windows组中删除默认架构。Windows用户不能使用NULL选项。–password=‘password’ [old_password=‘old_password’]–适用范围：SQL Server 2012 (11.x)到SQL Server 2017、SQL Database。–指定正在更改的用户的密码。 密码是区分大小写的。–old_password=‘old_password’–适用范围：SQL Server 2012 (11.x)到SQL Server 2017、SQL Database。–将替换为“password”的当前用户密码。密码是区分大小写的。–除非拥有ALTER ANY USER权限，否则需要具有OLD_PASSWORD才能更改密码。需要OLD_PASSWORD可防止拥有IMPERSONATION权限的用户更改密码。–此选项仅适用于包含的用户。–default_language={ none | <lcid> | <language_name> | <language alias> }–适用范围： SQL Server 2012 (11.x) 到 SQL Server 2017。–指定将指派给用户的默认语言。如果将此选项设置为NONE，则默认语言将设置为数据库的当前默认语言。如果之后更改数据库的默认语言，用户的默认语言将保持不变。–DEFAULT_LANGUAGE可以为本地 ID (lcid)、语言的名称或语言别名。–此选项只能在包含数据库中指定，且只能用于包含的用户。–allow_encrypted_value_modifications={ on | off }–适用范围：SQL Server 2016 (13.x)到SQL Server 2017、SQL Database。–取消在大容量复制操作期间对服务器进行加密元数据检查。 这使用户能够在表或数据库之间大容量复制加密数据，而无需对数据进行解密。 默认为 OFF。–description_name–用户自定义用户注释名称–description_value–用户自定义用户注释值示例/示例/–声明数据库引用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;goalter role [db_datareader] add member test1;go–删除成员身份alter role [db_backupoperator] drop member test1;goalter role [db_datareader] drop member test1;go –安全对象–授予权限–备份日志grant backup log to test1;go –扩展属性–删除扩展属性exec sys.sp_dropextendedproperty @name=N’tests_description’, @level0type=N’user’,@level0name=N’test1’go–添加扩展注释exec sys.sp_addextendedproperty @name=N’tests_description’, @value=N’用户自定义用户描述’, @level0type=N’user’,@level0name=N’test1’;go –修改当前数据库用户自定义用户属性alter user test1withname=test1,default_schema=dbo,–login=tests,–password=‘1234’ old_password=‘1234’,–default_language=English,allow_encrypted_value_modifications=off;go示例结果