备忘录--给自己

------========= 高山不弃杯土,故能就其大;江河不涓细流,故能成其长===========-------------

昨天，偶然发现的一个小知识点，以后也会把小知识点累积起来：

在SQL Server中创建用户角色及授权

参考文献

http://database.51cto.com/art/201009/224075.htm

正文

要想成功访问 SQL Server 数据库中的数据， 我们需要两个方面的授权：

获得准许连接 SQL Server 服务器的权利；
获得访问特定数据库中数据的权利（select, update, delete, create table ...）。
假设，我们准备建立一个 dba 数据库帐户，用来管理数据库 mydb。

1. 首先在 SQL Server 服务器级别，创建登陆帐户（create login）

--创建登陆帐户（create login）
create login dba with password='abcd1234@', default_database=mydb
登陆帐户名为：“dba”，登陆密码：abcd1234@”，默认连接到的数据库：“mydb”。 这时候，dba 帐户就可以连接到 SQL Server 服务器上了。但是此时还不能 访问数据库中的对象（严格的说，此时 dba 帐户默认是 guest 数据库用户身份， 可以访问 guest 能够访问的数据库对象）。

要使 dba 帐户能够在 mydb 数据库中访问自己需要的对象， 需要在数据库 mydb 中建立一个“数据库用户”，赋予这个“数据库用户” 某些访问权限，并且把登陆帐户“dba” 和这个“数据库用户” 映射起来。 习惯上，“数据库用户” 的名字和 “登陆帐户”的名字相同，即：“dba”。 创建“数据库用户”和建立映射关系只需要一步即可完成：

2. 创建数据库用户（create user）：

--为登陆账户创建数据库用户（create user）,在mydb数据库中的security中的user下可以找到新创建的dba
create user dba for login dba with default_schema=dbo
并指定数据库用户“dba” 的默认 schema 是“dbo”。这意味着 用户“dba” 在执行“select * from t”，实际上执行的是 “select * from dbo.t”。

3. 通过加入数据库角色，赋予数据库用户“dba”权限：

--通过加入数据库角色，赋予数据库用户“db_owner”权限
exec sp_addrolemember 'db_owner', 'dba'
此时，dba 就可以全权管理数据库 mydb 中的对象了。

如果想让 SQL Server 登陆帐户“dba”访问多个数据库，比如 mydb2。 可以让 sa 执行下面的语句：

复制代码
--让 SQL Server 登陆帐户“dba”访问多个数据库
use mydb2
go
create user dba for login dba with default_schema=dbo
go
exec sp_addrolemember 'db_owner', 'dba'
go
复制代码
此时，dba 就可以有两个数据库 mydb, mydb2 的管理权限了！

完整的代码示例


复制代码
--创建数据库mydb和mydb2

--在mydb和mydb2中创建测试表，默认是dbo这个schema
CREATE TABLE DEPT
       (DEPTNO int primary key,
        DNAME VARCHAR(14),
        LOC VARCHAR(13) );

--插入数据
INSERT INTO DEPT VALUES (101, 'ACCOUNTING', 'NEW YORK');
INSERT INTO DEPT VALUES (201, 'RESEARCH',   'DALLAS');
INSERT INTO DEPT VALUES (301, 'SALES',      'CHICAGO');
INSERT INTO DEPT VALUES (401, 'OPERATIONS', 'BOSTON');


--查看数据库schema, user 的存储过程
select * from sys.database_principals
select * from sys.schemas 
select * from sys.server_principals

--创建登陆帐户（create login）
create login dba with password='abcd1234@', default_database=mydb

--为登陆账户创建数据库用户（create user）,在mydb数据库中的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 mydb2
go
create user dba for login dba with default_schema=dbo
go
exec sp_addrolemember 'db_owner', 'dba'
go

--禁用登陆帐户
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

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

--删除 SQL Server登陆帐户： 
drop login dba
复制代码
使用存储过程来完成用户创建

下面一个实例来说明在sqlserver中如何使用存储过程创建角色，重建登录，以及如何为登录授权等问题。

复制代码
/*--示例说明
        示例在数据库InsideTSQL2008中创建一个拥有表HR.Employees的所有权限、拥有表Sales.Orders的SELECT权限的角色r_test
    随后创建了一个登录l_test，然后在数据库InsideTSQL2008中为登录l_test创建了用户账户u_test
    同时将用户账户u_test添加到角色r_test中，使其通过权限继承获取了与角色r_test一样的权限
    最后使用DENY语句拒绝了用户账户u_test对表HR.Employees的SELECT权限。
    经过这样的处理，使用l_test登录SQL Server实例后，它只具有表Sales.Orders的select权限和对表HR.Employees出select外的所有权限。
--*/


USE InsideTSQL2008

--创建角色 r_test
EXEC sp_addrole 'r_test'

--添加登录 l_test,设置密码为pwd,默认数据库为pubs
EXEC sp_addlogin 'l_test','a@cd123','InsideTSQL2008'

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

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


--用l_test登陆,发现在SSMS中找不到仍和表，因此执行下述两条语句出错。
select * from Sales.Orders
select * from HR.Employees

--授予角色 r_test 对 HR.Employees 表的所有权限
GRANT ALL ON HR.Employees TO r_test
--The ALL permission is deprecated and maintained only for compatibility. 
--It DOES NOT imply ALL permissions defined on the entity.
--ALL 权限已不再推荐使用，并且只保留用于兼容性目的。它并不表示对实体定义了 ALL 权限。

--测试可以查询表HR.Employees，但是Sales.Orders无法查询
select * from HR.Employees


--如果要收回权限，可以使用如下语句。（可选择执行）
revoke all on HR.Employees from r_test
--ALL 权限已不再推荐使用，并且只保留用于兼容性目的。它并不表示对实体定义了 ALL 权限。


--授予角色 r_test 对 Sales.Orders 表的 SELECT 权限
GRANT SELECT ON Sales.Orders TO r_test

--用l_test登陆,发现可以查询Sales.Orders和HR.Employees两张表
select * from Sales.Orders
select * from HR.Employees

--拒绝安全账户 u_test 对 HR.Employees 表的 SELECT 权限
DENY SELECT ON HR.Employees TO u_test

--再次执行查询HR.Employees表的语句，提示：拒绝了对对象 'Employees' (数据库 'InsideTSQL2008'，架构 'HR')的 SELECT 权限。
select * from HR.Employees

--重新授权
GRANT SELECT ON HR.Employees TO u_test

--再次查询，可以查询出结果。
select * from HR.Employees


USE InsideTSQL2008
--从数据库中删除安全账户,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就是收回已经给予的。

实例

按 Ctrl+C 复制代码

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权限。

在 SQL Server 2005 中数据完整性检查引发的次序如下：
1. DEFAULT 约束
2. NOT NULL 约束
3. CHECK 约束
4. FOREIGN KEY 约束
5. PRIMARY KEY 和 UNIQUE 约束
6. 触发器

　　--以下是完整的SQL执行语句
　　if exists(select * from syscolumns where id=object_id('数据表名称') and name='字段名') --判断该字段是否存在
　　begin
　　ALTER TABLE 表明 ALTER COLUMN 字段名 VARCHAR(64);--更改类型
　　end
　　GO
　　if exists(select * from syscolumns where id=object_id('表名') and name='旧字段名') --判断该字段是否存在
　　begin
　　EXEC sp_rename '表明。旧字段名', '新字段名', 'COLUMN'; --更改字段名称 sp_rename 为数据存储过程
　　end
　　GO

　　

　1、错误的删除操作：
　　--错误的临时表删除操作，因为所在数据库不同
　　IF EXISTS (SELECT * FROM sysobjects WHERE object_id = OBJECT_ID(N'[dbo].[#tempTable]') AND type in (N'U'))
　　Begin
　　DROP TABLE [dbo].[tempTable]
　　End
　　--错误的临时表删除操作，因为临时表名已变
　　if exists (select * from tempdb.dbo.sysobjects where id = object_id(N'[#temptable]'))
　　Begin
　　drop table #temptable
　　End
　　2、正确的删除方式：
　　--正确的临时表删除操作
　　if object_id('tempdb..#tempTable') is not null Begin
　　drop table #tempTable
　　End

　　

ALTER TABLE [x10].[DTM] WITH CHECK ADD CONSTRAINT [FK_DTM_tbl_tab_transaction] FOREIGN KEY([BatchID])
REFERENCES [dbo].[tbl_tab_transaction] ([BatchID])
ON UPDATE CASCADE
ON DELETE CASCADE

表级联设置

　　

dbcc inputbuffer(51)

27 开 3 次方跟
select power(27.0,(1.0/3))

把列,去空字符串, null 用 逗号连接
declare @name varchar(8000)=''
;with cte(name) as
(
 select 'a'
 union
 select ''
 union
 select ''
 union
 select 'c'
)
select @name=CASE WHEN right(@name,1)='' THEN @name+name--+','
      else COALESCE(@name+',','')+name end
     from cte
print @name

　　

where GAddress1>''
option (recompile);
update statistics tbltablename with all;

　　

SQL中只截取年月日或时分秒的转换！
select convert(varchar,getdate(),23)) //截取年月日
select convert(varchar,getdate(),8) //截取时分秒

生成随机 GUID
    DECLARE @dt VARCHAR(20) , 
        @guid VARCHAR(36)
    SET @guid = NEWID()
    SELECT  @dt = STUFF(RIGHT(CONVERT(UNIQUEIDENTIFIER, CONVERT(BINARY(16), GETDATE())),
                              12), 9, 0, '-')
    SELECT  @dt + RIGHT(@guid, 23)

　　

最简单的一个 SQL 注入：
 
;WITH temp(username,password) AS(
SELECT 'user1','psw1'
UNION ALL
SELECT 'user2','psw2'
UNION ALL
SELECT 'user3','psw3'
UNION ALL
SELECT 'user4','psw4'
)
SELECT * FROM temp WHERE username='' AND password='' OR '1' = '1'
 
--   ' OR '1' = '1

 ASCII 码为 13  
 对应为 10 

所以能看到很多地方 拼接 SQL 时候 char(13)+char(10) 联合换行，此外常见的

char(9) 为制表符 	

char(32) 为空格键

0-127 中有很多不可见字符

 

Declare @object_id int,

                @object_schema_name varchar(30)=object_schema_name(@object_id)

一直是有错误提示，原来在一个declare里面，未被识别，分开decalre就好了

-- 就 逗号跟 join 的 区别 做了一点测试：


;WITH a AS
(
  SELECT 'a' AS col
   UNION all 
  SELECT 'a' AS col
   UNION all
  SELECT 'b' AS col
   UNION all
  SELECT 'c' AS col
   UNION all
  SELECT 'b' AS col
   UNION all
  SELECT 'd' AS col
   UNION all
  SELECT 'f' AS col
)


SELECT  a.col AS acol,b.col AS bcol FROM a a , a b  

--没有加上 where 条件做筛选的时候应该是 笛卡尔积

WHERE a.col=b.col --加上 这个 筛选条件应该是 跟 inner join 等效。

Declare  @object_id int

Declare  @object_schema_name varchar(30)=object_schema_name(@object_id)

两种写法：

第一种：

 

--类似于通用表表达式
;WITH cte (NAME)
AS
(
SELECT 'Tom'
UNION
SELECT 'Jim'
UNION
SELECT 'Anna'
)
SELECT * FROM cte

第二种：

SELECT * FROM
(VALUES
('Tom'),
('Jim'),
('Anna')
) a (Name)
 

如果要给某个字符赋值千万注意 NULL+str的情况： null 连接任何东东都是null，所以打印也打印不出的。

IF (NULL+'abc')IS NULL PRINT 'null please don''t USE'

查询出各科目成绩第一，第二名，不通过排名函数，类似于查找各部门工资最高，第二高的。。。

object:  test就是一张学生成绩表


SELECT stu_subject,MAX(score) FROM test GROUP BY stu_subject
UNION 
SELECT a.stu_subject,MAX(a.score) AS score FROM test a INNER JOIN (SELECT stu_subject,MAX(score) AS score FROM test  GROUP BY stu_subject) AS b 
ON a.score!=b.score AND a.stu_subject=b.stu_subject
GROUP BY a.stu_subject

　　

Merge into 目的表 t

using 源表 s on t.col=s.col

when matched then 

update set t.col5=s.col5

when not matched by target （此处默认为by target） then

insert values(s.col1,s.col2,s.col3)

利用这个取代之前 insert where not exists ， update where exists  的写法。


下面是个例子：

DECLARE @test_tb TABLE(stu_name VARCHAR(30),stu_subject VARCHAR(30),score INT , Row_ID INT IDENTITY(1,1))
INSERT  INTO 
 @test_tb
SELECT 'Mary','Earth',80
UNION 
SELECT 'test','PE',200
MERGE INTO test t
USING 
(SELECT stu_name,stu_subject,score FROM @test_tb) tb ON (t.stu_name=tb.stu_name)
WHEN MATCHED THEN 
UPDATE SET t.score=tb.score
WHEN NOT MATCHED BY TARGET THEN 
INSERT VALUES(tb.stu_name,tb.stu_subject,tb.score);

SELECT * FROM test