34,594
社区成员
发帖
与我相关
我的任务
分享
--生成用户的语句
select 'create USER ['+ name+'] FOR login ['+name+'] WITH DEFAULT_SCHEMA =dbo'
from sysusers where uid<>1 and (uid<16384 or uid>16393)
AND name not in ('sys','public','guest','sa','dbo','INFORMATION_SCHEMA','login_user')
and name not like 'MSReplPAL%'
and name not like 'MStran%'
and name not like 'aspnet_%'
go
--删除用户的语句
select
case when (select count(*) from sys.schemas where name=s.name)>0 then
'drop schema ['+ name+']; drop user ['+name+'];'
else 'drop user ['+name+'];'end
from sysusers as s where uid<>1 and (uid<16384 or uid>16393)
AND name not in ('sys','public','guest','sa','dbo','INFORMATION_SCHEMA')
and name not like 'MSReplPAL%'
and name not like 'MStran%'
select
case a.protecttype when 204 then 'GRANT_W_GRANT' when 205 then 'GRANT'
when 206 then 'REVOKE' end /*as [protect_mode]*/+' '+
case a.action when 26 then 'REFERENCES' when 178 then 'CREATE FUNCTION'
when 193 then 'SELECT' when 195 then 'INSERT' when 196 then 'DELETE'
when 197 then 'UPDATE' when 198 then 'CREATE TABLE' when 203 then 'CREATE DATABASE'
when 207 then 'CREATE VIEW' when 222 then 'CREATE PROCEDURE' when 224 then 'EXECUTE'
when 228 then 'BACKUP DATABASE' when 233 then 'CREATE DEFAULT'
when 235 then 'BACKUP LOG' when 236 then 'CREATE RULE' end /*as [action_type]*/
+' ON ['+d.name /*as [object_owner]*/+'].['+b.name /*as [object_name]*/+']'
+' TO ['+c.name /*as [user_name]*/+']' as [protect_sql]
from sysprotects a (nolock)
join sysobjects b (nolock)
on a.id=b.id
join sysusers c (nolock)
on a.uid=c.uid
join sysusers d (nolock)
on b.uid=d.uid
where b.status>=0 and b.xtype in ('FN','IF','P','RF','TF','U','V','X')
and c.uid<>1 and (c.uid<16384 or c.uid>16393) and b.name not like '%!%'
and b.category!=2
下面代码在源数据库的master中执行,会新建两个存储过程,
这两个存储过程建立好后,执行存储过程EXEC master..sp_help_revlogin
在查询结果中生成的代码粘到目的机器中执行。就可以同步用户名和密码了。
/*
在源服务器上执行:EXEC master..sp_help_revlogin test
将上述输出进行编辑后在目标服务器上执行;
将源服务器上的用户所属组和权限同步到目标服务器上;
*/
----- Begin Script, Create sp_help_revlogin procedure -----
USE master
GO
IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE sp_hexadecimal
@binvalue varbinary(256),
@hexvalue varchar(256) OUTPUT
AS
DECLARE @charvalue varchar(256)
DECLARE @i int
DECLARE @length int
DECLARE @hexstring char(16)
SELECT @charvalue = '0x'
SELECT @i = 1
SELECT @length = DATALENGTH (@binvalue)
SELECT @hexstring = '0123456789ABCDEF'
WHILE (@i <= @length)
BEGIN
DECLARE @tempint int
DECLARE @firstint int
DECLARE @secondint int
SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
SELECT @firstint = FLOOR(@tempint/16)
SELECT @secondint = @tempint - (@firstint*16)
SELECT @charvalue = @charvalue +
SUBSTRING(@hexstring, @firstint+1, 1) +
SUBSTRING(@hexstring, @secondint+1, 1)
SELECT @i = @i + 1
END
SELECT @hexvalue = @charvalue
GO
IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
DROP PROCEDURE sp_help_revlogin
GO
CREATE PROCEDURE sp_help_revlogin
@login_name sysname = NULL
AS
DECLARE @name sysname,@dbname sysname
DECLARE @binpwd varbinary (256)
DECLARE @txtpwd sysname
DECLARE @tmpstr varchar (256)
DECLARE @SID_varbinary varbinary(85)
DECLARE @SID_string varchar(256)
IF (@login_name IS NULL)
DECLARE login_curs CURSOR FOR
-- SELECT a.sid, a.name as login, password,b.name as dbname FROM master..sysxlogins a join master..sysdatabases b
select sid,name,password_hash,default_database_name from sys.sql_logins
WHERE name <> 'sa'
ELSE
DECLARE login_curs CURSOR FOR
select sid,name,password_hash,default_database_name from sys.sql_logins
WHERE name = @login_name
OPEN login_curs
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @binpwd,@dbname
IF (@@fetch_status = -1)
BEGIN
PRINT 'No login(s) found.'
CLOSE login_curs
DEALLOCATE login_curs
RETURN -1
END
SET @tmpstr = '/* sp_help_revlogin script '
PRINT @tmpstr
SET @tmpstr = '** Generated '
+ CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
PRINT @tmpstr
PRINT ''
PRINT 'DECLARE @pwd sysname'
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
PRINT ''
SET @tmpstr = '-- Login: ' + @name
PRINT @tmpstr
IF (@binpwd IS NOT NULL)
BEGIN -- Non-null password
EXEC sp_hexadecimal @binpwd, @txtpwd OUT
-- IF (@xstatus & 2048) = 2048
-- SET @tmpstr = 'SET @pwd = CONVERT (varchar(256), ' + @txtpwd + ')'
SET @tmpstr = 'SET @pwd = CONVERT (varbinary(256), ' + @txtpwd + ')'
PRINT @tmpstr
EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name
+ ''', @pwd, @sid = ' + @SID_string + ',@defdb = '''+@dbname+''', @encryptopt = '
END
ELSE
BEGIN
-- Null password
EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name
+ ''', NULL, @sid = ' + @SID_string + ',@defdb = '''+@dbname+''', @encryptopt = '
END
SET @tmpstr = @tmpstr + '''skip_encryption'''
PRINT @tmpstr
END
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @binpwd,@dbname
END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO
----- End Script -----
我们公司有两个服务器,一个为正式使用,一个为测试用途,当从正式环境恢复到测试环境的时候,会出现原来用户无法访问的问题。但当你想加上原来用户的权限,系统会提示已经存在。但你并不没有看见,这个时候应该怎么办呢?
我们的组长提供了以下这个方法,
新建一个存储过程
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [sz_dvp_restore_login_user]
--INPUT
@DBName nvarchar(50),
@UserName nvarchar(50)
AS
Exec sp_configure 'allow updates',1
RECONFIGURE WITH OVERRIDE
Declare @ExecStr nvarchar(4000)
Select @ExecStr ='Declare @b varbinary(85) ' +
'Use Master '
+ 'Select @b = sid From syslogins Where Name =''' + @UserName + ''''
+ ' Use ' + @DBName
+ ' Update sysusers Set sid = @b Where name =''' + @UserName + ''''
--Print @ExecStr
Exec(@ExecStr)
Exec sp_configure 'allow updates',0
RECONFIGURE WITH OVERRIDE
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
http://www.phpchina.com/html/83/83_itemid_28969.html/*--示例说明
示例在数据库pubs中创建一个拥有表jobs的所有权限、拥有表titles的SELECT权限的角色r_test
随后创建了一个登录l_test,然后在数据库pubs中为登录l_test创建了用户账户u_test
同时将用户账户u_test添加到角色r_test中,使其通过权限继承获取了与角色r_test一样的权限
最后使用DENY语句拒绝了用户账户u_test对表titles的SELECT权限。
经过这样的处理,使用l_test登录SQL Server实例后,它只具有表jobs的所有权限。
--*/
USE pubs
--创建角色 r_test
EXEC sp_addrole 'r_test'
--授予 r_test 对 jobs 表的所有权限
GRANT ALL ON jobs TO r_test
--授予角色 r_test 对 titles 表的 SELECT 权限
GRANT SELECT ON titles TO r_test
--添加登录 l_test,设置密码为pwd,默认数据库为pubs
EXEC sp_addlogin 'l_test','pwd','pubs'
--为登录 l_test 在数据库 pubs 中添加安全账户 u_test
EXEC sp_grantdbaccess 'l_test','u_test'
--添加 u_test 为角色 r_test 的成员
EXEC sp_addrolemember 'r_test','u_test'
--拒绝安全账户 u_test 对 titles 表的 SELECT 权限
DENY SELECT ON titles TO u_test
/*--完成上述步骤后,用 l_test 登录,可以对jobs表进行所有操作,但无法对titles表查询,虽然角色 r_test 有titles表的select权限,但已经在安全账户中明确拒绝了对titles的select权限,所以l_test无titles表的select权限--*/
--从数据库 pubs 中删除安全账户
EXEC sp_revokedbaccess 'u_test'
--删除登录 l_test
EXEC sp_droplogin 'l_test'
--删除角色 r_test
EXEC sp_droprole 'r_test'