July 17, 2008 at 2:14 pm
We're migrating from 2000 to SQL Server 2005.
Looks like Backup/Restore will be chosen as
a way to implement the upgrade.
We're testing Backup/Restore
and look now at how Logins were created.
Question.
Will all Windows and SQL Server Logins
be created correctly and I don't have to worry about
it or I should prepare a script to create Logins/Roles
manually? Also because we just backup a database
only Logins/Roles on a database level will be created.
How about Logins we have in SQL 2000 on a server level?
(EM/Security/Logins)
Actually I still don't understand
if the Login or Role is created on a database level
do you have to do anything on a serer level
or, vice versa, if you create logins on a serer level
does it mean it will be mirrored on a database level automatically
and you don't need to do anything on a database level?
And in 2005, do I have to worry about all these new Securables,Principals,Schema? I have no idea what they are for.
July 17, 2008 at 3:06 pm
It depeneds how you are going to upgrade the server. If you will upgrade your server directly from 2k to 2k5, it will take care of security itself.
If you plan to restore your 2k db to a newly installed 2k5 server, sql logins will not be proper unless you fix them manually. Per me, using sp_help_revlogin is the bext way to fix it
Let me know if you need more info
July 18, 2008 at 6:40 am
I installed new SQL 2005.
It's empty. No databases.
I want to bring over to this 2005 server all 2000 stuff.
Actually it looks like Restore created all Logins/Roles correctly in 2005.
I tested two SQL Logins. They are members of "restrictedlist" Role in
commission database and Logins worked properly.
(I just had to switch manually to mixed mode authentication)
I'm just not sure if it works 100% for all Logins yet. My problem is I don't know if I can rely on Restore to take care of Security
or I need to manually fix it after Restore?
You mentioned "sp_help_revlogin" to fix Security
Do you mean "sp_help_revlogin_2000_to_2005"
from Microsoft website?
I tried that already.
In SQL 2000, for example, I have Role "restrictedlist" under commission database.
"sp_help_revlogin_2000_to_2005" does not generate any output for "restrictedlist". Maybe I miss some parameter when I call:
exec sp_help_revlogin_2000_to_2005 @login_name=NULL, @include_db=1, @include_role=1
I Actually looked at the source code of sp_help_revlogin
and couldn't find anythig about Database Role.
It only takes care of Server Roles.
See for yourself...
...
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb
END
END
IF @include_role = 1
BEGIN
PRINT ''
PRINT ''
PRINT ''
PRINT '/***** SET SERVER ROLES *****/'
FETCH FIRST FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb
WHILE @@fetch_status = 0
BEGIN
PRINT ''
SET @tmpstr = '-- Login: ' + @name
PRINT @tmpstr
IF @xstatus &16 = 16 -- sysadmin
BEGIN
SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''sysadmin'''
PRINT @tmpstr
END
IF @xstatus &32 = 32 -- securityadmin
BEGIN
SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''securityadmin'''
PRINT @tmpstr
END
IF @xstatus &64 = 64 -- serveradmin
BEGIN
SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''serveradmin'''
PRINT @tmpstr
END
IF @xstatus &128 = 128 -- setupadmin
BEGIN
SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''setupadmin'''
PRINT @tmpstr
END
IF @xstatus &256 = 256 --processadmin
BEGIN
SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''processadmin'''
PRINT @tmpstr
END
IF @xstatus &512 = 512 -- diskadmin
BEGIN
SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''diskadmin'''
PRINT @tmpstr
END
IF @xstatus &1024 = 1024 -- dbcreator
BEGIN
SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''dbcreator'''
PRINT @tmpstr
END
IF @xstatus &4096 = 4096 -- bulkadmin
BEGIN
SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''bulkadmin'''
PRINT @tmpstr
END
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb
END
...
July 18, 2008 at 12:20 pm
hi,
if some other databases already exists in the new server then
u can restore the database and create the role and in the security logins already user mapping might have done to access the database just select the new db restored and add to role.
if no db exits in new server then create role u have to map user manually
try this and tell me.
July 18, 2008 at 12:43 pm
Anjan,
So in your opinion you need to do some manual stuff
after Restore, like create a Role, for example, and add Login to this Role.
Basically it means sp_revlogin is not enough. Right?
July 18, 2008 at 3:49 pm
This is how it works...
A SQL DB typically has SQL Login and/or Domain Login
Each Login is mapped to a Server Login
When you move a DB to a new server, it takes all DB Login but can not take server level logins.
So.. Windows login will work provided new Server is in same domain/trusted forest. You only need to make sure that for each DB Domain login, you have server login. Otherwise just grant login to the domain to SQL Server and SQL will take care of all
For SQL, it is issue. You can not directly create SQL Login at server level as you will not know the password for SQL Account. (If you know... you create them manually). Otherwise sp_help_revlogin will take care of that problem
Hope it helps
July 21, 2008 at 7:39 am
Hi Utsab,
Both old and new servers are on the same DOMAIN.
You wrote
"...You only need to make sure that for each DB Domain login, you have server login..."
Does it mean I have to manually create Server level Logins?
This is where I lack knowledge on SQL Server Security.
I don't understand it.
If you have all your Security setup OK on a Database level,
do you have to worry about anything on a Server level?
For example, I have a SQL Login "javasvr" an it's a member of
"restrictedlist" Role in the Database "ComXXX".
Do I need worry about Server level Security for this particular Login
after Backup/Restore?
And another scenario.
I have Windows Login "ACCOUNTS\wchan" and it's a member of "exec_procs" Role. Do I have to worry about Server level security for this Login after Backup/Restore?
July 21, 2008 at 8:05 am
Actually I did a test.
I tried to create SQL Login "NewGuy"
and got error:
Error 15007: the Login "NewGuy" does not exist.
So first, one has to create a Login on a Server level
and only then you will be able to create the same Database Login?
(I'm trying to understand the sequence of steps in setting up the Security.
What's first, what's second)
So how the Backup/Restore even allows
to create a new SQL Login if it's not created yet on a Server level?
Or Backup/Restore does take care of the Login on a Server level?
(I think I need to do another test)
July 21, 2008 at 10:06 am
I decided to do my own investigation.
I created a Common2 database on SQL 2000 box.
1.
On this box on Server level I created SQL Login "yegorov"
without any mapping to databases.
SQL Server Login Properties: public
2.
Under database "Common2" I created User "yegorov"
and Role "Yegorov_Family".
I granted EXEC permissions on a couple of tables for "Yegorov_Family" Role.
Verified with QA. Login works.
3.
I did a Backup of "Common2" database.
Restored it on SQL Server 2005 machine.
Checked if under "Common2" database User "yegorov" was created
and Role "Yegorov_Family" was created.
Yes, all is there.
Tried to login as "yegorov".
Login error.
4.
Created Server level Login:
CREATE LOGIN [yegorov]
WITH PASSWORD=N'yegorov',
DEFAULT_DATABASE=[Common2],
CHECK_EXPIRATION=OFF,
CHECK_POLICY=OFF
Tried to login as "yegorov".
Login error.
--Intermediate conclusion--
After backup/Restore simply creating Server level Login "yegorov" will not be enough.
5.
On a Server level in [Security/Login Properties/User Mappings]
for Login "yegorov" checked "Common2" database.
Database role membership for: Common2 = public
This action generated the following code:
USE [Common2]
GO
CREATE USER [yegorov] FOR LOGIN [yegorov]
GO
Try to run it in QA
Got error message:
Msg 15023, Level 16, State 1, Line 1
User, group, or role 'yegorov' already exists in the current database.
Now I'm confused.
Does it mean I have to delete all Users created after Restore
and re-create them manually?
Is this where sp_revlogin becomes handy?
July 21, 2008 at 1:21 pm
I figured it out.
My Step 5 should be:
use Common2
EXEC sp_change_users_login 'Update_One', 'yegorov', 'yegorov';
and it fixes a link between SQL Login "yegorov" and User "yegorov".
(updates SID)
So to summarize.
After restoring database "Common2" on
a new SQL Server 2005 box here is what you need to do
to enable login for database user "yegorov":
If exists (
SELECT [name] FROM sys.server_principals WHERE Type = 'S' and [name] = 'yegorov'
)
DROP LOGIN [yegorov]
GO
USE [master]
GO
CREATE LOGIN [yegorov]
WITH PASSWORD=N'yegorov',
DEFAULT_DATABASE=[Common2],
CHECK_EXPIRATION=OFF,
CHECK_POLICY=OFF
GO
use Common2
EXEC sp_change_users_login 'Update_One', 'yegorov', 'yegorov';
GO
July 21, 2008 at 2:30 pm
Guys,
Actually what I suggested before is not good.
It will create SQL Login on 2005 but the way I
implement this is not good.
The drawback is that using this option you need to
know all your SQl Logins passwords. Do you have to?
No.
Here is a better solution.
After you did Backup and Restore on 2005,
run this script on your 2000 server:
select 'exec sp_addlogin @loginame = ''' + Convert(Varchar(50),Loginname) + ''', @passwd = ', Convert(varbinary(256), password), ', @defdb = ''' + Convert(varchar(50),Dbname) + ''', @sid = ', convert(varbinary(32),sid), ', @encryptopt = ''skip_encryption'''
From Syslogins
where isntname = 0
and LoginName = 'yegorov'
It will generate the line for you.
Copy this line and run it in SQl Server 2005.
So here are the steps after Backup/Restore:
--1. drop SQL Login "yegorov" if exists
If exists (
SELECT [name] FROM sys.server_principals WHERE Type = 'S' and [name] = 'yegorov'
)
DROP LOGIN [yegorov]
GO
--2. create SQL Login on 2005 with exactly the same properties as it was on 2000
exec sp_addlogin
@loginame = 'yegorov',
@passwd = 0x0100253ABF217ED25BA1A0492AFD2D6D29A130A1B8E58140F5E711A5BB7EAAEE901BDD4208217F3B6F2FCC5E3555,
@defdb = 'Common2',
@sid = 0xBB22421901AA374BB849DBFE9C05BCBD,
@encryptopt = 'skip_encryption'
SQL Login "yegorov" is re-created now on 2005
and you don't even need to know it's password on 2000.
SQL Server took care of it.
Much better way to transfer SQL Logins.
Especially if you have lots of SQL Logins and you don't know all the passwords.
July 21, 2008 at 2:42 pm
Riga
I am so sorry for the late.. I was not online for past couple of days :crying:
Glad to know you figured it by yourself; Please let me know if you want any more details. I will send my mail id by personal message so that I can try to help directly if you need on it; (This way I hope it will not be delay. I get my mails on phone)
July 21, 2008 at 2:45 pm
Could not find the way to send message. You may mail me at query@consultdba.com
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply