Method required to transfer repeatedly users in a

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

High Availability (2005)

Method required to transfer repeatedly users in a

Author

Topic

GrahamK
Starting Member

5 Posts

Posted - 2009-07-30 : 09:37:06

Dear All,

I rather urgently need a method of transferring SQL server logins from primary to secondary server, so that in the case of failover of the database there is no loss of service availability to our users.

Thus i require it in a method which can be run into a SQL Server agent job and run repeatedly to keep the user accounts on both servers up to date.

If somebody could tell me concisely how this can be achieved. I would be most grateful.

Many THanks
Graham

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2009-07-30 : 19:54:26

This should get you started:

select
p.name,
N'CREATE LOGIN [' + p.name + N']' +
case
when p.type = 'S'
then N' WITH PASSWORD = ' + master.dbo.fn_hexadecimal(sl.password_hash) + N' HASHED' +
case
when p.default_database_name is null
then N''
else N', DEFAULT_DATABASE = ' + p.default_database_name + N' '
end +
case
when p.default_language_name is null
then N''
else N', DEFAULT_LANGUAGE = ' + p.default_language_name + N' '
end +
N', CHECK_EXPIRATION = ' +
case when sl.is_expiration_checked = 1 then N'ON ' else N'OFF ' end +
N', CHECK_POLICY = ' +
case when sl.is_policy_checked = 1 then N'ON ' else N'OFF ' end
when p.type in ('U', 'G')
then N' FROM WINDOWS' +
case
when (p.default_database_name is not Null and p.default_language_name is not Null)
then N' WITH DEFAULT_DATABASE = ' + p.default_database_name +
N', DEFAULT_LANGUAGE = ' + p.default_language_name
when (p.default_database_name is not Null)
then N' WITH DEFAULT_DATABASE = ' + p.default_database_name
when (p.default_language_name is not Null)
then N' WITH DEFAULT_LANGUAGE = ' + p.default_language_name
else N''
end
end + ';' sql
from
sys.server_principals p
left outer join
sys.sql_logins sl
on
p.principal_id = sl.principal_id
where
p.type in ('S', 'U', 'G')
order by p.name;

The fn_hexadecimal function is from Microsoft. You can google (er..um..well...) You can BING for the source code; it's free.

=======================================
Men build too many walls and not enough bridges. -Isaac Newton, philosopher and mathematician (1642-1727)

GrahamK
Starting Member

5 Posts

Posted - 2009-08-03 : 03:59:38

Hi,

Many Thanks for that. I am a little unsure how I take this Select statement and wrap it into something I can use to perform the task as I require above... Also, I don't appear to be able to find the fn_hexadecimal anywhere online. Please can you post a link to it please?

Many Thanks
Graham

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources