【自学笔记】在SQL Server中创建用户角色及授权(使用SQL语句)更新2023.07.06

这篇具有很好参考价值的文章主要介绍了【自学笔记】在SQL Server中创建用户角色及授权(使用SQL语句)更新2023.07.06。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。

【自学笔记】在SQL Server中创建用户角色及授权(使用SQL语句)更新2023.07.06,sqlserver

--<在SQL Server中创建用户角色及授权(使用SQL语句)>更新2023.07.06
--1. 首先在 SQL Server 服务器级别,创建登陆帐户(create login)
--2. 创建数据库用户(create user):
--3. 通过加入数据库角色,赋予数据库用户“dba”权限:

--创建登陆帐户(create login)
create login dba with password='abcd1234@', default_database=AliSysDB
--这时候,dba 帐户就可以连接到 SQL Server 服务器上了。但是此时还不能 访问数据库中的对象
--(严格的说,此时 dba 帐户默认是 guest 数据库用户身份, 可以访问 guest 能够访问的数据库对象)。
--要使 dba 帐户能够在 AliSysDB 数据库中访问自己需要的对象, 需要在数据库 AliSysDB 中建立一个“数据库用户”,
--赋予这个“数据库用户” 某些访问权限,并且把登陆帐户“dba” 和这个“数据库用户” 映射起来。
--创建“数据库用户”和建立映射关系只需要一步即可完成

--为登陆账户创建数据库用户(create user),在AliSysDB数据库中的security中的user下可以找到新创建的dba
create user dba for login dba with default_schema=dbo
--通过加入数据库角色,赋予数据库用户“db_owner”权限
exec sp_addrolemember 'db_owner', 'dba'

----让 SQL Server 登陆帐户“dba”访问多个数据库
use DBErp
create user dba for login dba with default_schema=dbo
exec sp_addrolemember 'db_owner', 'dba'


-->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

--删除数据库用户:
drop user dba

--删除 SQL Server登陆帐户:
drop login dba



--禁用登陆帐户
alter login dba disable
--启用登陆帐户
alter login dba enable

--登陆帐户改名
alter login dba with name=dba_tom
--登陆帐户改密码:
alter login dba with password='aabb@ccdd'

--数据库用户改名:
alter user dba with name=dba_tom
--更改数据库用户 defult_schema:
alter user dba with default_schema=sales
--使用T-SQL创建用户
--添加角色
use AliSysDB
go
sp_addlogin  'test_db','123456','AliSysDB' 
go
sp_addsrvrolemember   'test_db','sysadmin'   
go
sp_adduser 'test_db','test_db','db_owner'
go  
--使用存储过程来完成用户创建
--下面一个实例来说明在sqlserver中如何使用存储过程创建角色,重建登录,以及如何为登录授权等问题



USE DBErp
 
--创建角色 r_test
EXEC sp_addrole 'r_test'
--添加登录 l_test,设置密码为pwd,默认数据库为pubs
EXEC sp_addlogin 'l_test','a@cd123','DBErp'

--为登录 l_test 在数据库 pubs 中添加安全账户 u_test
EXEC sp_grantdbaccess 'l_test','u_test'

--添加 u_test 为角色 r_test 的成员
EXEC sp_addrolemember 'r_test','u_test'

--授予角色 r_test 对 效期查询 表的所有权限
GRANT ALL ON 效期查询 TO r_test
--ALL 权限已不再推荐使用,并且只保留用于兼容性目的。它并不表示对实体定义了 ALL 权限。

--如果要收回权限,可以使用如下语句。(可选择执行)
revoke all on 效期查询 from r_test

--授予角色 r_test 对 实时库存查询 表的 SELECT 权限
GRANT SELECT ON 实时库存查询 TO r_test


--用l_test登陆,发现可以查询Sales.Orders和实时库存查询两张表
select * from 效期查询
select * from 实时库存查询

--拒绝安全账户 u_test 对 效期查询 表的 SELECT 权限
DENY SELECT ON 效期查询 TO u_test

--重新授权
GRANT SELECT ON 效期查询 TO u_test


--从数据库中删除安全账户,failed
EXEC sp_revokedbaccess 'u_test'

--删除角色 r_test,failed
EXEC sp_droprole 'r_test'

--删除登录 l_test,success
EXEC sp_droplogin 'l_test'



--revoke 与 deny的区别
--revoke:收回之前被授予的权限
--deny:拒绝给当前数据库内的安全帐户授予权限并防止安全帐户通过其组或角色成员资格继承权限。比如UserA所在的角色组有inset权限,但是我们Deny UserA使其没有insert权限,那么以后即使UserA再怎么到其他含有Insert的角色组中去,还是没有insert权限,除非该用户被显示授权。
--简单来说,deny就是将来都不许给,revoke就是收回已经给予的。



GRANT INSERT ON TableA TO RoleA
GO
EXEC sp_addrolemember RoleA, 'UserA' -- 用户UserA将有TableA的INSERT权限
GO
 
REVOKE INSERT ON TableA FROM RoleA -- 用户UserA将没有TableA的INSERT权限,收回权限
GO
 
GRANT INSERT ON TableA TORoleA --重新给RoleA以TableA的INSERT权限
GO 
 
DENY INSERT ON TableA TO UserA -- 虽然用户UserA所在RoleA有TableA的INSERT权限,但UserA本身被DENY了,所以用户UserA将没有TableA的INSERT权限。





使用SSMS数据库管理工具创建用户登录,这个可视化操作比起用sql语句来创建是在是简单多了

一丶创建用户

登录数据库,在安全性→登录名(鼠标右击)→新建登录名

在弹出新建登录名窗口的“常规”中,输入登录名和密码,密码一定要设置复杂一点,要不然会报错,如果想设置简单的密码也是可以的,把“强制实施密码策略”√去掉就行了

【自学笔记】在SQL Server中创建用户角色及授权(使用SQL语句)更新2023.07.06,sqlserver

二丶设置管理员权限

点击左边选择页中的“服务器角色”,来给新建用户授予权限,在右侧的服务器角色面板中,勾选public

注意:如果建立的账号不需要具备系统管理员的权限时,则不要勾选sysadmin 项!

【自学笔记】在SQL Server中创建用户角色及授权(使用SQL语句)更新2023.07.06,sqlserver

服务器角色    说明
sysadmin    执行SQL Server中的任何操作
serveradmin    配置服务器设置
setupadmin    安装复制和管理扩展过程
securityadmin    管理登录和CREATE DATABASE的权限以及阅读审计
processadmin    管理SQL Server进程
dbcreator    创建和修改数据库
diskadmin    管理磁盘文件

【自学笔记】在SQL Server中创建用户角色及授权(使用SQL语句)更新2023.07.06,sqlserver

三丶设置非管理员权限

点击左边选择页中的“用户映射”,选择用户可以登录的数据库、选择用户拥有的登录数据库的权限。并在该面板下面的【数据库角色成员身份】中勾选db_owner项!

【自学笔记】在SQL Server中创建用户角色及授权(使用SQL语句)更新2023.07.06,sqlserver

注意:如果给你个无系统管理权限的账号指定管理一个数据库时,则一定要勾选db_owner项!否则该账号无法看到该数据库中的任何数据表!

让新建的用户dba对多个数据库都拥有权限,
接着上步继续做,再点其他数据库,然后在勾选下面相应的权限即可。这样一次性就能对用户赋予多个数据库不同的权限。

【自学笔记】在SQL Server中创建用户角色及授权(使用SQL语句)更新2023.07.06,sqlserver

'db_owner' --拥有数据库全部权限,包括删除数据库权限
'db_accessadmin' --只给数据库用户创建其他数据库用户的权限,而没有创建登录用户的权限。
'db_securityadmin' --可以管理全部权限、对象所有权、角色和角色成员资格
'db_ddladmin' --可以发出所有DDL(Create,Alter和Drop),但不能发出GRANT、REVOKE或DENY语句
'db_backupoperator' --允许对数据库进行备份和还原的权限【备份与还原是通过sql sever management studio也可以进行】
'db_datareader' --可以选择数据库内任何用户表中的所有数据
'db_datawriter' --可以更改数据库内任何用户表中的所有数据
'db_denydatareader' --不能查询数据库内任何用户表中的任何数据
'db_denydatawriter' --不能更改数据库内任何用户表中的任何数据
 

【自学笔记】在SQL Server中创建用户角色及授权(使用SQL语句)更新2023.07.06,sqlserver

4、点击左边选择页中的“安全对象”,选择安全对象后,然后再授权。

【自学笔记】在SQL Server中创建用户角色及授权(使用SQL语句)更新2023.07.06,sqlserver

四丶进入【状态】设置连接引擎授权

最后点击“状态”,授予连接到数据库引擎,登录名启用,最后点击确定就可以了

【自学笔记】在SQL Server中创建用户角色及授权(使用SQL语句)更新2023.07.06,sqlserver

无法登陆情况
请检查数据库属性页【安全性】是否启用 SQL Server 和 windows 验证模式;文章来源地址https://www.toymoban.com/news/detail-532101.html

到了这里,关于【自学笔记】在SQL Server中创建用户角色及授权(使用SQL语句)更新2023.07.06的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

  • RabbitMQ创建新用户,并给用户添加角色和授权

      或             注:先进入到容器内部 注: 此处的角色只是一个标签(角色有 administrator、monitoring、policymaker、management 等等),也可以自己自定义 RabbitMQ角色释义 administrator policymaker和monitoring可以做的任何事外加: 创建和删除virtual hosts 查看、创建和删除users 查看创建和删除pe

    2024年02月03日
    浏览(47)
  • MongoDB 6.0 单实例基于用户角色实现授权登录

    现代数据库系统能够存储和处理大量数据。因此,由任何一个用户单独负责处理与管理数据库相关的所有活动的情况相对较少。通常,不同的数据库用户需要对数据库的某些部分具有不同级别的访问权限:某些用户可能只需要读取特定数据库中的数据,而其他用户则必须能够

    2024年02月05日
    浏览(35)
  • SQL Server用户定义的函数(UDF)使用详解

    与编程语言中的函数一样,SQL Server 用户定义函数是接受参数、执行操作(如复杂计算)并将该操作的结果作为值返回的例程。返回值可以是单个标量值,也可以是结果集。 模块化编程。可以创建一次函数,将其存储在数据库中,并在程序中调用它任意次数。可以独立于程序

    2023年04月12日
    浏览(46)
  • BI-SQL丨角色和用户

    角色和用户 在数仓的运维工作中,经常需要为用户开通不同权限的账号,使用户可以正常访问不同的数据,那么这就需要我们了解SQL Server的权限体系。 名词解释 登录名: 用来登录服务器的用户账号,例:sa,这个就是默认的可以登录SQL Server服务器的超级管理员账号。 用户

    2024年02月10日
    浏览(30)
  • postgresql|数据库|角色(用户)管理工作---授权和去权以及usage和select两种权限的区别

    postgresql做为一个比较复杂的关系型的重型数据库,不管是安装部署,还是后期的运行维护,都还是有比较多的细节问题需要引起关注。 例如,用户权限的合理分配,那么,什么是权限的合理分配呢? 自然是权限的最小化原则,也就是说每个用户能够完成其权限范围内的工作

    2024年02月13日
    浏览(49)
  • PostgreSQL学习笔记(一):数据库、模式、表空间、用户、用户角色

        本文讨论所说的都是 PostgreSQL 数据库的相关定义内容,其中名词以及翻译如下: 数据库: database 模式: schema 表: table 表空间: tablespace 用户: use 用户角色: role PostgreSQL 通用的语法:     在 PostgreSQL 数据库中,数据库(database)可以认为是物理层面数据集合。当使

    2024年02月11日
    浏览(41)
  • Ubuntu Server版 之 mysql 系列(-),安装、远程连接,mysql 创建用户、授权等

    Ubuntu 分 桌面版 和 服务版 桌面版 :有额外的简易界面 服务版:是纯黑框的。没有任何UI界面的可言 安装mysql 8.0 最新的ubuntu商店里面mysql 是 8.0版本的 安装位置 一般按照的位置存放在 /usr/bin 中 修改root 密码 8.0版本 退出程序或应用 exit 或 Ctrl +D 查看mysql的状态 service mysql st

    2024年02月15日
    浏览(74)
  • 小程序学习笔记---获取用户授权

    在小程序开发中,获取用户授权是一个非常重要的步骤。通过获取用户授权,我们可以获取用户的基本信息、地理位置等敏感信息,从而为用户提供更加个性化的服务。在本文中,我们将探讨如何在小程序中获取用户授权。 1. 授权方式 小程序获取用户授权有两种方式:一种

    2024年02月09日
    浏览(34)
  • SQL Server实例间同步登录用户

    问题痛点 :由于AlwaysOn和数据库镜像无法同步数据库外实例对象,例如 登录用户、作业、链接服务器等,导致主库切换之后,应用连接不上数据库或者作业不存在导致每晚跑批任务漏跑等 目前来看,作业等其他实例对象的同步还比较难实现,比如作业分为很多步骤,而且作

    2024年02月09日
    浏览(39)
  • Sql Server 创建用户并限制权限

    使用sa或者Windows身份验证登录,【安全性】-【登录名】,右键【新建登录名】 db owner --拥有数据库全部权限,包括删除数据库权限 db accessadmin --只给数据库用户创建其他数据库用户的权限,而没有创建登录用户的权限 db securityadmin --可以管理全部权限、对象所有权、角色和角

    2024年01月19日
    浏览(39)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包