SQL Server初探

SQL Server的结构与Oracle不同，SQL Server里边可以包括很多的database，每个database有自己的表，用户等信息。比如目前有一个应用，应用的每个数据集都是一个database，致使在SQL Server中有多达1000+的database。

我们可以通过以下语句查看SQL Server中的所有数据库。

SELECT Name FROM Master..SysDatabases ORDER BY Name

SQL Server的用户也分为两种，一种是登录SQL Server的用户，一种是SQL Server中每个数据库中的用户。通常情况下，我们会将登录用户与数据库用户取同样的名字，然后通过用户映射赋予登录用户权限。

创建登录SQL Server用户语句如下：

--创建登录用户“dba”，密码为abcd1234@，默认数据库是mydb
create login dba with password='abcd1234@', default_database=mydb

创建数据库中的用户的语句如下：

--创建数据库用户“dba”，与登录用户同名
create user dba for login dba with default_schema=dbo

赋予数据库用户相应的权限

--通过加入数据库角色，赋予数据库用户“db_owner”权限
exec sp_addrolemember 'db_owner', 'dba'

其他相关语句可参考这边博文，点我。

在维护某系统时，需要根据数据库命名规则批量detach一些数据库，比如数据库名已R16开头的，通过以下SQL可以拼出sql语句，然后批量执行。

select 'EXEC  sp_detach_db ['+[name]+']'+CHAR(10) from master..sysdatabases where substring(name, 1,3) in ('E15','E16','R15','R16') ORDER BY Name

===============================================================================

下面说说我遇到的需求，一个SQL Server数据库有1000多个database。需要建一个用户，使这个用户对每个database具有只读权限，需要写脚本实现。无意中在互联网上发现了一篇博文。与我的需求完全一致，准备在测试环境里边实验一下。

也不是原创，直接把代码贴过来吧。

Use master
GO

DECLARE @dbname VARCHAR(50)   
DECLARE @statement NVARCHAR(max)

DECLARE db_cursor CURSOR 
LOCAL FAST_FORWARD
FOR  
SELECT name
FROM MASTER.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb','distribution')  
OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @dbname  
WHILE @@FETCH_STATUS = 0  
BEGIN  

SELECT @statement = 'use '+@dbname +';'+ 'CREATE USER [TipsDemoUser] 
FOR LOGIN [TipsDemoUser]; EXEC sp_addrolemember N''db_datareader'', 
[TipsDemoUser];EXEC sp_addrolemember N''db_datawriter'', [TipsDemoUser]'

exec sp_executesql @statement

FETCH NEXT FROM db_cursor INTO @dbname  
END  
CLOSE db_cursor  
DEALLOCATE db_cursor 

 这篇博文也可参考。

======================================================================

具体执行上述脚本遇到的问题：

1、拼的@statement这个sql中必须给@dbname两边加上"["和"]"，要不然汇报语法错误，折腾了好久；

2、实际的数据库中可能有只读的库，执行到只读库的时候脚本就终止了，所以需要将只读的库剔除。

通过互联网，找到两种找到只读库的方法。一是通过databasepropertyex这个函数得到数据库的读写状态，具体说明见微软官方文档。

以下是我写的代码，将所有只读库放到一个中间表里边。但是最终也没有成功，主要是不能把动态SQL的结果传给一个变量，下面用的方法也是网上找到，但是没有成功。

use master 
go
declare @dbname_ro varchar(100)
declare @statement_ro nvarchar(max)
declare @states_ro nvarchar(20)
declare db_cursor_ro cursor
local fast_forward
for 
SELECT name
FROM MASTER.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb','distribution')
open db_cursor_ro
fetch next from db_cursor_ro into @dbname_ro
while @@fetch_status = 0
begin
select @statement_ro = 'select databasepropertyex(' + '''' + @dbname_ro + '''' + ',''updateability'')'
exec sp_executesql @statement_ro, N'@states_ro nvarchar(20) output', @states_ro output
if @states_ro = 'READ_ONLY'
    insert into [ProgramUse].dbo.database_ro(db_names, db_state) values ('@dbname_ro', 'READ_ONLY')
fetch next from db_cursor_ro into @dbname_ro
end
close db_cursor_ro
deallocate db_cursor_ro

最后通过系统的一个视图可以方便的查找只读的数据库，sys.databases，其中is_read_only为1的就是只读状态的数据库。

最终完成为所有数据库建立一个只读用户的目的。最终代码如下：

Use master
GO
DECLARE @dbname VARCHAR(100)
DECLARE @statement NVARCHAR(max)
DECLARE db_cursor CURSOR
LOCAL FAST_FORWARD
FOR
SELECT name
FROM MASTER.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb','distribution','IED2008_JPEFEI_CT_JPY_EDM110') and name not in (select name from sys.databases where is_read_only = 1)
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @statement = 'use '+ '[' + @dbname + ']' + ';' + 'CREATE USER reapsro FOR LOGIN reapsro; EXEC sp_addrolemember ''db_datareader'',''reapsro'''
exec sp_executesql @statement
FETCH NEXT FROM db_cursor INTO @dbname
END
CLOSE db_cursor
DEALLOCATE db_cursor