34,837
社区成员
发帖
与我相关
我的任务
分享如何快速回复用户权限
我发布了几个订阅服务器，但没有建立登录账号和用户，如何把生产机上的登录账号和用户，以及权限移植到订阅服务器上？
有没有一个办法，在生产机上把某个用户的权限生成一个脚本，拿到订阅服务器上执行？如果为每个对象重新分配权限，太耗时了，也不安全！
有没有一个办法，在生产机上把某个用户的权限生成一个脚本，拿到订阅服务器上执行？如果为每个对象重新分配权限，太耗时了，也不安全！
...全文
请发表友善的回复…
发表回复
nzperfect 2009-04-24
- 打赏
- 举报
--生成用户的语句
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%'
nzperfect 2009-04-24
- 打赏
- 举报
生成权限脚本：
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!=2nzperfect 2009-04-24
- 打赏
- 举报
同步用户名和密码及sid:
下面代码在源数据库的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 -----
renzhm 2009-04-24
- 打赏
- 举报
sql2005
让你望见影子的墙 2009-04-24
- 打赏
- 举报
关注MM大叔
nzperfect 2009-04-24
- 打赏
- 举报
sql 2005 ?还是sql 2000?
renzhm 2009-04-24
- 打赏
- 举报
楼上老大，如何导出权限脚本，我现在要的就是这个
nzperfect 2009-04-24
- 打赏
- 举报
如果帐号权限是数据库级的，很简单，只同步login帐号就可以.
如果帐号权限到表级，那么要导出权限脚本到订阅去执行.
如果帐号权限到表级，那么要导出权限脚本到订阅去执行.
renzhm 2009-04-24
- 打赏
- 举报
用恢复的方法，只是会造成用户孤立，这个好解决，我当时做订阅服务器时，用的是空数据库，除了用户和权限，其他数据库对象都已经同步了
lg3605119 2009-04-24
- 打赏
- 举报
帮顶
htl258_Tony 2009-04-24
- 打赏
- 举报
再不行就只能帮顶了.
htl258_Tony 2009-04-24
- 打赏
- 举报
我们公司有两个服务器，一个为正式使用，一个为测试用途，当从正式环境恢复到测试环境的时候，会出现原来用户无法访问的问题。但当你想加上原来用户的权限，系统会提示已经存在。但你并不没有看见，这个时候应该怎么办呢？
我们的组长提供了以下这个方法，
新建一个存储过程
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
youqi1984 2009-04-24
- 打赏
- 举报
只有顶的份了
claro 2009-04-24
- 打赏
- 举报
帮顶
win的 powershell不知能否实现。
win的 powershell不知能否实现。
Andy__Huang 2009-04-24
- 打赏
- 举报
一般先把旧用户权限等信息删除,然后再建立用户\授权等,这样做比较保险,保证数据库访问不会出错
完成这个操作一般在企业管理器里完成
使用命令还要知道参数的使用,心理上觉得不是那么保险?
完成这个操作一般在企业管理器里完成
使用命令还要知道参数的使用,心理上觉得不是那么保险?
ws_hgo 2009-04-24
- 打赏
- 举报
关注
renzhm 2009-04-24
- 打赏
- 举报
楼上方法是创建时有用，我现在有比较复杂的用户权限，而且对象繁多，不想去写脚本，而是想从生产机上的权限备份出来，恢复到订阅服务器上
htl258_Tony 2009-04-24
- 打赏
- 举报
/*--示例说明
示例在数据库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'
王向飞 2009-04-24
- 打赏
- 举报
学习了 谢谢
renzhm 2009-04-24
- 打赏
- 举报
非常感谢perfectaction ，太帅了
