在SQL Server数据库中查找用户帐户?
问题描述:
有没有办法在SQL Server上的数据库中找到特定的用户帐户?在SQL Server数据库中查找用户帐户?
通常情况下,您可以将帐户添加到SQL Server,此时您还可以将用户映射到该服务器上的多个数据库。只要没有人做坏事,您可以返回到服务器上的帐户属性,查看帐户映射到的数据库。
但是,如果帐户存在于数据库中,则此连接可能很容易中断,但它不会在服务器级别显示为映射到该数据库。此外,如果服务器上已存在一个帐户,则无法在其中一个数据库中创建帐户,这是我处于的情况。
有100个数据库,我需要找到该用户帐户的数据库据我所知,sys.database_principals我们每个数据库都是唯一的。是否可以为一个帐户进行跨数据库搜索并返回它存在的数据库?
答
以下是一些帮助查询。
声明@returnValue诠释 EXEC @returnValue =的sp_change_users_login 'Update_One', 'SXAUtil', 'SXAUtil' 打印 '/ @的returnValue /' 打印@returnValue 打印 ''
select * from master..syslogins where name='MyUser001'
select * from [MyDatabase001]..sysusers where name='MyUser001'
select 1 as 'Match??' , mastSysLogin.sid as mastSysLogin_Sid, localSysLogin.sid as localSysLogin_Sid, mastSysLogin.name as mastSysLogin_Name , localSysLogin.name as localSysLogin_Name from master..syslogins mastSysLogin join [MyDatabase001]..sysusers localSysLogin on mastSysLogin.sid = localSysLogin.sid
where mastSysLogin.name='MyUser001'
go
此过程我认为不赞成,但研究它会显示一些“缺失的链接”信息。
Use MyDatabase001
GO
declare @returnValue int
EXEC @returnValue = sp_change_users_login 'Update_One', 'MyUser001', 'MyUser001'
print '/@returnValue/'
print @returnValue
print ''
下面是我写的脚本,用于将用户添加到登录名和dbusers并映射到某些角色。
它使用SQLCMD模式。但是,随着我试图编写代码来创建用户,这是一个“多年”的努力,如果用户已经添加,它不会引发错误。
/*
--SqlCmd Notes:
--Remove comments and space between ":" and "setvar" to run in sqlcmd mode.
--!!! Checked in code MUST *recomment out* the setvars below !!! (Putting a space between the ":" and the "setvar" is sufficient)
-- (This is not preferred behavior, the issue has been reported at http://connect.microsoft.com/SQLServer/feedback/details/382007/in-sqlcmd-scripts-setvar-should-have-a-lower-precedence-than-command-line-variable-assignments)
: setvar ErrorOutputFileFromCommandLine "c:\wuwutemp\sqlcommmanderrorfile.txt"
: setvar DBNAME "MyDatabaseName"
: setvar DBUSERNAME "MyDomain\someDomainUserThatExists"
*/
Use [$(DBNAME)]
GO
:Error $(ErrorOutputFileFromCommandLine)
declare @databaseName varchar(64)
select @databaseName = db_name()
print '/@databaseName/'
print @databaseName
print ''
set nocount on
declare @CONST_DB_DATAREADER_ROLE varchar(64)
select @CONST_DB_DATAREADER_ROLE = 'db_datareader'
declare @CONST_DB_BACKUPOPERATOR_ROLE varchar(64)
select @CONST_DB_BACKUPOPERATOR_ROLE = 'db_backupoperator'
declare @UserNameHolder table (IdentityID int identity(101,1) , DatabaseName varchar(128) , LoginName varchar(256) , MassagedLoginName varchar(264) , RoleName varchar(64) not null , AlreadyProcessed bit default 0 not null)
INSERT INTO @UserNameHolder (DatabaseName , LoginName , RoleName) values ('$(DBNAME)' , '$(DBUSERNAME)' , @CONST_DB_DATAREADER_ROLE)
--INSERT INTO @UserNameHolder (DatabaseName , LoginName , RoleName) values ('$(DBNAME)' , '$(DBUSERNAME)' , @CONST_DB_BACKUPOPERATOR_ROLE)
/* Below this line is "common logic code" */
Update @UserNameHolder Set MassagedLoginName = LoginName
/*
sp_grantlogin does NOT like brackets around a domain/user
example : EXEC sp_grantlogin @loginame = N'[MyDomain\MyUser]'
The "massaging" below is a work around to this issue
*/
Update @UserNameHolder Set MassagedLoginName = RIGHT(MassagedLoginName , LEN(MassagedLoginName) -1) where LEN(MassagedLoginName)>0 AND LEFT(MassagedLoginName,1)='['
Update @UserNameHolder Set MassagedLoginName = LEFT(MassagedLoginName , LEN(MassagedLoginName) -1) where LEN(MassagedLoginName)>0 AND RIGHT(MassagedLoginName,1)=']'
/* Use a iffy rule to figure out NT Names */
--Update @UserNameHolder Set IsNTName = 1 where LEN(LoginName)>0 AND LEFT(LoginName,1)='[' AND RIGHT(LoginName,1)=']'
-- public is built in, do not process it
Update @UserNameHolder Set AlreadyProcessed = 1 where UPPER(LoginName) = UPPER('public')
select * from @UserNameHolder
declare @CurrentIdentityID int
declare @CurrentLoginName varchar(256)
declare @CurrentMassagedLoginName varchar(256)
declare @CurrentDatabaseName varchar(64)
--declare @CurrentIsNTName bit
declare @CurrentRoleName varchar(64)
declare @sp_grantdbaccess_return_value_total int
select @sp_grantdbaccess_return_value_total = 0
declare @sp_addrolemember_return_value_total int
select @sp_addrolemember_return_value_total = 0
while exists (select null from @UserNameHolder where AlreadyProcessed = 0)
begin
select @CurrentIdentityID = (select top 1 IdentityID from @UserNameHolder where AlreadyProcessed = 0)
print '@CurrentIdentityID'
print @CurrentIdentityID
print ''
select @CurrentLoginName = (select top 1 LoginName from @UserNameHolder where IdentityID = @CurrentIdentityID)
select @CurrentMassagedLoginName = (select top 1 MassagedLoginName from @UserNameHolder where IdentityID = @CurrentIdentityID)
select @CurrentDatabaseName = (select top 1 DatabaseName from @UserNameHolder where IdentityID = @CurrentIdentityID)
--select @CurrentIsNTName = (select top 1 IsNTName from @UserNameHolder where IdentityID = @CurrentIdentityID)
select @CurrentRoleName = (select top 1 RoleName from @UserNameHolder where IdentityID = @CurrentIdentityID)
print '@CurrentLoginName'
print @CurrentLoginName
print ''
print '@CurrentMassagedLoginName'
print @CurrentMassagedLoginName
print ''
print '@CurrentDatabaseName'
print @CurrentDatabaseName
print ''
--print '@CurrentIsNTName'
--print @CurrentIsNTName
--print ''
print '@CurrentRoleName'
print @CurrentRoleName
print ''
if(1=1) /* 1=1 is helpful while debugging, set to 1=2 to exclude */
begin
-- select name, loginname from master.dbo.syslogins where loginname = @currentLoginName and isntname = 1)
if not exists (select null from master.dbo.syslogins where UPPER(loginname) = UPPER(@CurrentLoginName)) -- and isntname = 1)
begin
print 'About to execute sp_grantlogin for user:'
print @CurrentMassagedLoginName
EXEC sp_grantlogin @loginame = @CurrentMassagedLoginName
print ''
print ''
print 'About to execute sp_defaultdb for user:'
print @CurrentMassagedLoginName
EXEC sp_defaultdb @loginame = @CurrentMassagedLoginName , @defdb = @CurrentDatabaseName
print ''
print ''
end
else
begin
print 'NOT EXECUTED : sp_grantlogin'
print 'NOT EXECUTED : sp_defaultdb'
end
end
----------------------------------------
declare @sp_grantdbaccess_return_value int
declare @sp_change_users_login_return_value int
declare @needToRunThisIteration bit
select @needToRunThisIteration = 0
-- declare @currentLoginName varchar(64)
if(1=1) /* 1=1 is helpful while debugging, set to 1=2 to exclude */
begin
if not exists
(
select * from master.dbo.syslogins ml inner join dbo.sysusers
su on ml.sid = su.sid where
su.uid < 6382
and
UPPER(ml.name) = UPPER(@CurrentMassagedLoginName)
)
begin
if not exists
(
/* NOTE, this database name variable must be updated if copying/pasting 'common logic code' between database deployments (NEED_TO_REPLACE_A_VALUE) */
select * from master.dbo.syslogins ml Where
UPPER(ml.name) = UPPER(@CurrentMassagedLoginName)
and UPPER(ml.dbname) = UPPER('$(DBNAME)')
)
begin
select @needToRunThisIteration = 1
end
end
if not exists
(
/* NOTE, this database name variable must be updated if copying/pasting 'common logic code' between database deployments (NEED_TO_REPLACE_A_VALUE) */
select * FROM [$(DBNAME)]..sysusers su Where
UPPER(su.name) = UPPER(@CurrentMassagedLoginName)
)
begin
select @needToRunThisIteration = 1
end
/*
Debug
select dbname , loginname , * from master.dbo.syslogins ml where dbname != 'master' order by 1 , 2
select * FROM TPA2Report..sysusers su
*/
declare @stringNameMatchesButNotSid bit
select @stringNameMatchesButNotSid = 0
IF EXISTS (
SELECT dbUsers.name as NameToFix , *
FROM sys.sysusers dbUsers
join sys.syslogins sqlServerLogins on UPPER(dbUsers.name) = UPPER(sqlServerLogins.name)
WHERE dbUsers.sid != sqlServerLogins.sid
and UPPER(dbUsers.name) = UPPER(@CurrentMassagedLoginName)
)
begin
select @stringNameMatchesButNotSid = 1
end
if (@needToRunThisIteration!=0)
begin
if @stringNameMatchesButNotSid = 0
begin
print 'About to execute sp_grantdbaccess for user:'
print @CurrentMassagedLoginName
exec @sp_grantdbaccess_return_value = sp_grantdbaccess @CurrentMassagedLoginName
print ''
print '@sp_grantdbaccess_return_value'
print @sp_grantdbaccess_return_value
print ''
select @sp_grantdbaccess_return_value_total = @sp_grantdbaccess_return_value_total + @sp_grantdbaccess_return_value
end
else
begin
/* There is a mismatched "sid" issue, but with the same names. This will address the issue. */
print 'About to execute sp_change_users_login for user:'
print @CurrentMassagedLoginName
EXEC @sp_change_users_login_return_value = sp_change_users_login @Action='update_one', @[email protected],@[email protected];
print ''
print '@sp_change_users_login_return_value'
print @sp_change_users_login_return_value
print ''
end
end
end
-------------------------------------------------------
declare @sp_addrolemember_return_value int
if(1=1) /* 1=1 is helpful while debugging, set to 1=2 to exclude */
begin
if not exists
(
SELECT null --, role_principal_id, member_principal_id, princ1.name AS
role_name, princ2.name As member_name
FROM sys.database_role_members AS S LEFT JOIN sys.database_principals AS
princ1 ON S.role_principal_id = princ1.principal_id AND princ1.type = 'R'
LEFT JOIN sys.database_principals AS princ2 ON S.member_principal_id =
princ2.principal_id AND princ2.type IN ('S', 'U')
WHERE princ1.name = @CurrentRoleName and UPPER(princ2.name) = UPPER(@CurrentMassagedLoginName)
)
begin
print 'About to execute sp_grantdbaccess for user:'
print @CurrentMassagedLoginName
print 'For the role:'
print @CurrentRoleName
exec @sp_addrolemember_return_value = sp_addrolemember @CurrentRoleName, @CurrentMassagedLoginName
print ''
print '@sp_addrolemember_return_value'
print @sp_addrolemember_return_value
print ''
select @sp_addrolemember_return_value_total = @sp_addrolemember_return_value_total + @sp_addrolemember_return_value
end
end
Update @UserNameHolder Set AlreadyProcessed = 1 Where IdentityID = @CurrentIdentityID
end
print '/@sp_grantdbaccess_return_value_total/'
print @sp_grantdbaccess_return_value_total
print '/@sp_addrolemember_return_value_total/'
print @sp_addrolemember_return_value_total
GO