问题描述
我想在两个sql服务器实例上创建许多数据库用户.为了能够轻松地将数据库从一个实例复制到另一个实例,我希望SID保持一致.
I want to create a number of database users on two sql server instances. To be able to easily copy databases from one instance to another, I want the SIDs to be consistent.
我的想法是使用可预测的SID,以最大程度地减少维护麻烦.我梦到这样的事情:
My idea was to use predictable SIDs, to minimize maintenance pain. I a dreaming about something like this:
CREATE LOGIN newuser1 WITH PASSWORD = '...', SID = CAST('newuser1' AS BINARY(16))
-- or alternatively:
CREATE LOGIN newuser1 WITH PASSWORD = '...', SID = HASHBYTES('newuser1')
但是,CREATE LOGIN
命令似乎不支持计算SID的值:
Calculating values for the SID, however, seems not to be supported by the CREATE LOGIN
command:
Meldung 102, Ebene 15, Status 1, Zeile 1
Falsche Syntax in der Nähe von "SID". -- Wrong syntax near "SID"
如何为SID
使用计算值?
推荐答案
这可以通过将sql语句组装到变量中并用sp_executesql
执行它来实现:
This can be accomplished by assembling the sql statement in a variable and the executing it with sp_executesql
:
DECLARE @SQL nvarchar(4000)
SET @SQL = 'CREATE LOGIN newuser1 WITH PASSWORD = ''...'', SID = 0x'+CONVERT(VARCHAR(1000), CAST('newuser1' AS binary(16)), 2)
PRINT @SQL
EXEC sp_executesql @SQL
或者,如果您想重用这种方法,则可以使用创建存储过程:
Alternatively, if you want to reuse this approach, you can use create a stored procedure:
CREATE PROCEDURE create_user_with_predictable_sid
@UserName varchar(256),
@Password varchar(256)
AS
DECLARE @SQL nvarchar(4000) = 'CREATE LOGIN '+@UserName+' WITH PASSWORD = '''+@Password+''', SID = 0x'+CONVERT(VARCHAR(1000), CAST(@UserName AS binary(16)), 2)
PRINT @SQL
EXEC sp_executesql @SQL
GO
GO
create_user_with_predictable_sid 'newuser1', '...'
GO
DROP PROCEDURE create_user_with_predictable_sid
这篇关于如何计算新登录的SID?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!