Problem in Database Restore

Hi
I am facing the following issue when trying to restore a database from a log
in that has 'dbcreator' role.
While restore, i get the following error :
'Processed 104 pages for database 'Testsql', file 'TestSQL_Data' on file 1.
Processed 1 pages for database 'Testsql', file 'TestSQL_Log' on file 1.
Server: Msg 916, Level 14, State 1, Line 1
Server user 'TestSQl' is not a valid user in database 'Testsql'.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.'
If i login with a role of 'sysadmin', i find that the data has been restored
completely
but the link with the login and sysusers in the database has been broken.
The problem is that, i don't have the sysadmin role in the production server
and i have to restore my database with the 'dbcreator' role only.
Should i have the 'sysadmin' role for database restore to be complete?
With Thanks,
Jeyalakshmi.b> If i login with a role of 'sysadmin', i find that the data has been
restored completely
> but the link with the login and sysusers in the database has been broken.
This is normal when restoring/attaching a database from another server. You
can resync logins and users with sp_change_users_login. See the Books
Online <"tsqlref.chm::/ts_sp_ca-cz_8qzy.htm"> for details.

> The problem is that, i don't have the sysadmin role in the production
server
> and i have to restore my database with the 'dbcreator' role only.
>
To restore the database with only dbcreator, the login either needs to be
the original database owner or a user in the source database. Also, the
login's SID needs to be the same on both servers. The SID will be always be
the same on both servers with Windows authentication but not with SQL
authentication. With SQL authentication, you can specify the desired SQL
login SID with the sp_addlogin @.sid parameter.

> Should i have the 'sysadmin' role for database restore to be complete?
Restores are bit easier with sysadmin role membership since you don't have
sync the dbcreator login on both servers. However, it's not a requirement
to be a sysadmin role member as described above.
Hope this helps.
Dan Guzman
SQL Server MVP
"Jeyalakshmi" <anonymous@.discussions.microsoft.com> wrote in message
news:5D0837F5-2FB5-4899-89A4-B4FDA2B33F47@.microsoft.com...
> Hi
> I am facing the following issue when trying to restore a database from a
login that has 'dbcreator' role.
> While restore, i get the following error :
> 'Processed 104 pages for database 'Testsql', file 'TestSQL_Data' on file
1.
> Processed 1 pages for database 'Testsql', file 'TestSQL_Log' on file 1.
> Server: Msg 916, Level 14, State 1, Line 1
> Server user 'TestSQl' is not a valid user in database 'Testsql'.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.'
> If i login with a role of 'sysadmin', i find that the data has been
restored completely
> but the link with the login and sysusers in the database has been broken.
> The problem is that, i don't have the sysadmin role in the production
server
> and i have to restore my database with the 'dbcreator' role only.
> Should i have the 'sysadmin' role for database restore to be complete?
> With Thanks,
> Jeyalakshmi.b
>
>
>
>

Problem creating a stored procedure with the Execute as clause.

I have a server with a login named NewCreator. This login is assigned only to the dbcreator server role.

I want this login to be able to execute the stored procedure, sp_helplogins.

The sp_helplogins documentation says the caller of this proecedure needs securityadmin permissions.

I know that I should be able to create a login, say 'SecAdmin' and assign that login to the security admin server role, and then grant NewCreator Impersonate permission on SecAdmin.

What I don't like about this technique is that NewCreator can get on the server and gain complete authority over security when I really only want him to be able to execute sp_helplogins.

Therefore, what I am attempting is the following:

I create the login, SecAdmin, and assign it to the securityadmin server role.

Then I right click sp_helplogins and select 'Modify'.

I change the Alter to Create, change the name of the procedure to sp_myspecialone, and then add the sql statement "With Execute as 'SecAdmin'.

I execute the above and the new stored procedure is created. I then grant Execute on sp_myspecialone to NewCreator.

Everything works fine so far. But when I login as NewCreator and execute sp_myspecialone, I get an error in line 73 - no permission.

Line 73 is blank, but the next few lines read:

if(not(is_srvrolemember('securityadmin')= 1))

begin

raiserror(15247,-1,-1)

select @.RetCode = 1

goto label_86return

end

What am I doing wrong? Is their a better alternative to accomplish what I want to do?

Your assistance would be greatly appreciated.

It looks like when executing "AS SecAdmin" you are only executing the proc in the context of the user not the login. Therefore, it won't resolve the server permissions of the login SecAdmin but just the database permissions of the user SecAdmin.

In your scenario check:

execute as login = 'SecAdmin'

SELECT is_srvrolemember('securityadmin')

execute as user = 'SecAdmin'

SELECT is_srvrolemember('securityadmin')

I'm afraid i don't have any alternatives at this time but i'll have a think.

Hope this helps for now.

|||

Actually, is it possible to change the proc to be something along the lines of:

CREATE PROC blah
AS

EXECUTE AS LOGIN = 'SecAdmin'

EXEC sp_helplogins

Problem creating a stored procedure with the Execute as clause.

I have a server with a login named NewCreator. This login is assigned only to the dbcreator server role.

I want this login to be able to execute the stored procedure, sp_helplogins.

The sp_helplogins documentation says the caller of this proecedure needs securityadmin permissions.

I know that I should be able to create a login, say 'SecAdmin' and assign that login to the security admin server role, and then grant NewCreator Impersonate permission on SecAdmin.

What I don't like about this technique is that NewCreator can get on the server and gain complete authority over security when I really only want him to be able to execute sp_helplogins.

Therefore, what I am attempting is the following:

I create the login, SecAdmin, and assign it to the securityadmin server role.

Then I right click sp_helplogins and select 'Modify'.

I change the Alter to Create, change the name of the procedure to sp_myspecialone, and then add the sql statement "With Execute as 'SecAdmin'.

I execute the above and the new stored procedure is created. I then grant Execute on sp_myspecialone to NewCreator.

Everything works fine so far. But when I login as NewCreator and execute sp_myspecialone, I get an error in line 73 - no permission.

Line 73 is blank, but the next few lines read:

if (not (is_srvrolemember('securityadmin') = 1))

begin

raiserror(15247,-1,-1)

select @.RetCode = 1

goto label_86return

end

What am I doing wrong? Is their a better alternative to accomplish what I want to do?

Your assistance would be greatly appreciated.

It looks like when executing "AS SecAdmin" you are only executing the proc in the context of the user not the login. Therefore, it won't resolve the server permissions of the login SecAdmin but just the database permissions of the user SecAdmin.

In your scenario check:

execute as login = 'SecAdmin'

SELECT is_srvrolemember('securityadmin')

execute as user = 'SecAdmin'

SELECT is_srvrolemember('securityadmin')

I'm afraid i don't have any alternatives at this time but i'll have a think.

Hope this helps for now.

|||

Actually, is it possible to change the proc to be something along the lines of:

CREATE PROC blah
AS

EXECUTE AS LOGIN = 'SecAdmin'

EXEC sp_helplogins