数据库使用

use fycp_v40_log
go
create user team_fycp for login team_fycp with default_schema=dbo;
exec sp_addrolemember ‘db_owner’,’team_fycp’
create user team_read_only for login team_read_only with default_schema=dbo;
exec sp_addrolemember ‘db_datareader’,’team_read_only’

创建用户

—创建的用户无任何权限，只能登陆
—create login：在 SQL Server 服务器级别，创建登陆帐户
USE [Master]
IF NOT EXISTS(SELECT name FROM sys.sql_logins WHERE NAME=’abc’)
BEGIN
CREATE LOGIN [abc] WITH PASSWORD=’123456’,CHECK_EXPIRATION = OFF,CHECK_POLICY=OFF;
GRANT VIEW SERVER STATE TO [abc];
GRANT ALTER TRACE TO [abc];
—GRANT VIEW ANY database to [abc];
—DROP LOGIN abc
END

—create user： 创建数据库用户，注意 use 数据库 上下文，只有创建了数据库用户才能为用户赋予角色权限，下面三个写法都是一样的效果
—如果需要访问数据库还需要分配权限
USE [Test]
if exists (select name from sys.sysusers where issqluser=1 and name =’abc’)
DROP USER abc
create user [abc] for login [abc] with default_schema=dbo;

—分配只读权限，把只读角色db_datareader分配给loginName，下面两条语句一样的效果
exec sp_addrolemember ‘db_datareader’,’loginName’
ALTER ROLE [db_datareader] ADD MEMBER [abc]

—分配读写权限（只能修改表数据）
exec sp_addrolemember ‘db_datawriter’,’abc’

—分配读写权限（修改表数据和数据库结构）
exec sp_addrolemember ‘db_ddladmin’,’abc’

—分配Dbowner权限
exec sp_addrolemember ‘db_owner’,’abc’

—更改当前数据库的Dbowner（不能存在user，如果存在必须要 DROP USER user）
if exists (select name from sys.sysusers where issqluser=1 and name =’abc’)
DROP USER abc
EXEC dbo.sp_changedbowner N’abc’

数据库级别的角色名称及说明

角色名称 说明
db_owner 可以执行数据库的所有配置和维护活动，还可以删除数据库
db_securityadmin 可以修改角色成员身份和管理权限。向此角色中添加主体可能会导致意外的权限升级
db_accessadmin 可以为 Windows 登录名、Windows 组和 SQL Server 登录名添加或删除数据库访问权限
db_backupoperator 可以备份数据库
db_ddladmin 可以在数据库中运行任何数据定义语言 (DDL) 命令
db_datawriter 可以在所有用户表中添加、删除或更改数据
db_datareader 可以从所有用户表中读取所有数据
db_denydatawriter 禁止添加、修改或删除数据库内用户表中的任何数据
db_denydatareader 禁止读取数据库内用户表中的任何数据

开启/关闭某个账号所有库的查看权限

USE master
GO
—开启
GRANT VIEW ANY DEFINITION TO team_read_only

—关闭
DENY VIEW ANY DEFINITION TO team_read_only

针对数据库某一张表添加权限

删除用户

—查看当前数据库所有连接用户所有进程Id
select loginame,spid from master.dbo.sysprocesses
where dbid=db_id(‘DbName’)

—查看数据库所有连接
exec sp_who
exec sp_who sa
exec sp_who2
—kill 52

—从当前数据库删除用户 test
—位置：服务器-》数据库-》展开数据库-》安全性-》用户
USE [Student]
if exists(select * from sys.sql_logins where name=’test’)
drop user ‘test’

—从 SQL Server 实例中删除登录用户实例 test
if exists(select * from sys.sql_logins where name=’test’)
drop login test

————————————下面的不常用———————————————-
—从当前数据库删除角色（角色和用户不同。权限是与角色相关联的，而用户与角色关联，一个用户可以绑定多个角色）
—位置：服务器-》数据库-》展开数据库-》安全性-》角色
if exists(select * from sys.database_principals where name=’role’)
drop role role;

—删除角色的指定用户
—db_datareader 角色
—test 用户
alter role [db_datareader] drop member test;