| Groups | Blog | Home |
sql server programming : Ownership of newly attached database when using sp_attach_db?
to have a non-sysadmin user be able to attach and detach a database, as well as be the owner of the database it attaches. In SQL Server 2005, I've created a login called "Maint". I have given this user a Server Role of "dbcreator". When I log in as Maint, I can attach a database, and it shows "Maint" as being the owner of the database. But when I go "USE Maint_db" it says Maint doesn't have access: Msg 916, Level 14, State 1, Line 1 The server principal "Maint" is not able to access the database "Maint_DB" under the current security context. Then I go to the Login Properties for the Maint login, and under user mappings everything is blank. I expected to see Maint_DB have a user of Maint with a Default Schema of dbo. If I go to the Maint_DB properties, it does show Maint as the owner. What am I missing? If I change the database owner to sa, and then back to Maint, everything works as I would expect: Maint is the true owner of the database and can access all objects in it. Can anyone point me to what I am doing wrong? I would like to be able to attach a database and set it's owner to Maint from a stored procedure, while giving Maint only the minimal required access. I tried running it as a sysadmin, and that sure made things work well. :) Thank you in advance. Ken
Here's some more info. When creating a new database, it works fine.
The "Maint" login becomes the owner of the new database. Here is my test script: ------------------------- -- CREATE NEW DATABASE -- ------------------------- USE MASTER GO CREATE DATABASE Maint_DB ON PRIMARY ( NAME = N'Maint_DB_data', FILENAME = N'D:\Maint_DB.mdf' , SIZE = 2048KB , FILEGROWTH = 1024KB ) LOG ON ( NAME = N'Maint_DB_log', FILENAME = N'D:\Maint_DB.ldf' , SIZE = 2048KB , FILEGROWTH = 10%) GO SELECT * FROM MAINT_DB.dbo.sysobjects GO DROP DATABASE Maint_DB go ------------------------- But when I try it when attaching an existing database, it fails. It definitely has to do with the current user not properly becoming the owner of the database. If I go in as a sysadmin and add "Maint" to be a db_owner, everything then works. Here is my test script: ------------------------------ -- ATTACH EXISTING DATABASE -- ------------------------------ USE MASTER GO CREATE DATABASE [Maint_DB] ON ( FILENAME = N'D:\ExistingDB.MDF' ), ( FILENAME = N'D:\ExistingDB.LDF' ) FOR ATTACH GO SELECT * FROM MAINT_DB.dbo.sysobjects GO ------------------------ Any suggestions, other than "make Maint a sysadmin"? Thanks.
[quoted text, click to view]
kenandcorey@gmail.com wrote: > [...] > Then I go to the Login Properties for the Maint login, and under user > mappings everything is blank. I expected to see Maint_DB have a user of > Maint with a Default Schema of dbo. > [...] Hi, Ken Try executing a: use Maint_DB exec sp_change_users_login 'Auto_Fix', 'Maint' Razvan
[quoted text, click to view]
Razvan Socol wrote: > kenandcorey@gmail.com wrote: > > [...] > > Then I go to the Login Properties for the Maint login, and under user > > mappings everything is blank. I expected to see Maint_DB have a user of > > Maint with a Default Schema of dbo. > > [...] > > Hi, Ken > > Try executing a: > use Maint_DB > exec sp_change_users_login 'Auto_Fix', 'Maint' > > Razvan Thank you for responding. I have tried that, but when logged in as Maint3 I can't switch to the Maint_DB database. It gives me the error: The server principal "Maint3" is not able to access the database "Maint_DB" under the current security context. I also tried it as SA, and it didn't help either. Any other ideas? Ken
Here's what I think is going on:
Each database has a users table, exposed as sys.database_principals. Here you can see the SID of the owner of the database (dbo, see the sid column). Detach this on one instance, and you will have some sid for a login on that instance. Now, attach into another instance, and the size from above doesn't exist on the new instance. You have an orphaned owner. Seen from the database perspective. However, in master, there's a table for each database, exposed as sys.databases. There's a sid stored here as well, owner_sid. When you attach, the information in sys.databases (in master) is set to the login who attaches the database. But the information *inside* the database isn't set accordingly. Hence the inconsistency in the SSMS dialogs. If you now change the database owner (sp_change_dbowner), the sid *inside* the database is set correctly. So the solution seems to be to set the owner properly after attaching the database. -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ [quoted text, click to view] <kenandcorey@gmail.com> wrote in message
news:1162278829.923464.274290@m7g2000cwm.googlegroups.com... >I am confused about database ownership when using sp_attach_db. I want > to have a non-sysadmin user be able to attach and detach a database, as > well as be the owner of the database it attaches. > > In SQL Server 2005, I've created a login called "Maint". I have given > this user a Server Role of "dbcreator". When I log in as Maint, I can > attach a database, and it shows "Maint" as being the owner of the > database. But when I go "USE Maint_db" it says Maint doesn't have > access: > > Msg 916, Level 14, State 1, Line 1 > The server principal "Maint" is not able to access the database > "Maint_DB" under the current security context. > > Then I go to the Login Properties for the Maint login, and under user > mappings everything is blank. I expected to see Maint_DB have a user of > Maint with a Default Schema of dbo. > > If I go to the Maint_DB properties, it does show Maint as the owner. > > What am I missing? If I change the database owner to sa, and then back > to Maint, everything works as I would expect: Maint is the true owner > of the database and can access all objects in it. > > Can anyone point me to what I am doing wrong? I would like to be able > to attach a database and set it's owner to Maint from a stored > procedure, while giving Maint only the minimal required access. I tried > running it as a sysadmin, and that sure made things work well. :) > > Thank you in advance. > > Ken >
Hi, Ken
Try to execute this (under the sa login): USE Maint_DB EXEC sp_change_users_login 'update_one', 'Maint', 'Maint' If it works, then the problem is probably related to the value for the SID for the Maint login. The database probably originates from a different server, where Maint had a different SID. Am I close ? Razvan [quoted text, click to view] kenandcorey@gmail.com wrote:
> Razvan Socol wrote: > > kenandcorey@gmail.com wrote: > > > [...] > > > Then I go to the Login Properties for the Maint login, and under user > > > mappings everything is blank. I expected to see Maint_DB have a user of > > > Maint with a Default Schema of dbo. > > > [...] > > > > Hi, Ken > > > > Try executing a: > > use Maint_DB > > exec sp_change_users_login 'Auto_Fix', 'Maint' > > > > Razvan > > Thank you for responding. I have tried that, but when logged in as > Maint3 I can't switch to the Maint_DB database. It gives me the error: > > The server principal "Maint3" is not able to access the database > "Maint_DB" under the current security context. > > I also tried it as SA, and it didn't help either. > > Any other ideas? > > Ken
Hi Razvan, Thank you for taking the time to reply. I did try what you
suggested, but it didn't work either. I will soon post a follow-up to Tibor's reply, and it will explain what I think is happening. I think I have a better understanding now with everyone's help. Thanks agao. Ken [quoted text, click to view] On Nov 2, 5:48 am, "Razvan Socol" <rso...@gmail.com> wrote:
> Hi, Ken > > Try to execute this (under the sa login): > > USE Maint_DB > EXEC sp_change_users_login 'update_one', 'Maint', 'Maint' > > If it works, then the problem is probably related to the value for the > SID for the Maint login. The database probably originates from a > different server, where Maint had a different SID. Am I close ? > > Razvan > > kenandco...@gmail.com wrote: > > Razvan Socol wrote: > > > kenandco...@gmail.com wrote: > > > > [...] > > > > Then I go to the Login Properties for the Maint login, and under user > > > > mappings everything is blank. I expected to see Maint_DB have a user of > > > > Maint with a Default Schema of dbo. > > > > [...] > > > > Hi, Ken > > > > Try executing a: > > > use Maint_DB > > > exec sp_change_users_login 'Auto_Fix', 'Maint' > > > > Razvan > > > Thank you for responding. I have tried that, but when logged in as > > Maint3 I can't switch to the Maint_DB database. It gives me the error: > > > The server principal "Maint3" is not able to access the database > > "Maint_DB" under the current security context. > > > I also tried it as SA, and it didn't help either. > > > Any other ideas? > > > Ken
Hello Tibor,
Thank you for taking the time to reply. Your suggestions helped me to pin down what I think the problem is. The show-stopper is that to run sp_change_dbowner you need to be able to switch to the database (USE databasename). Unfortunately I can't do this when logged in as the Maint user, as he doesn't even exist in the sys.database_principals table for the Maint_DB database. So to work around this, I plan on making sure the Maint user exists in the Maint_DB database before I attach it. Then when I do attach it using the Maint user, it WILL have proper access to the database. Since I'll always be attaching it to the same database server, the SIDs shouldn't get out of sync -- at least that is my understanding. I plan to do the following: - As a sysadmin - Attach the database - make Maint the owner - Detach the database Now, as the Maint user, whenever I attach the database I am already the dbo and everything is good. My confusion was in the fact that the login attaching the database does not automatically become the dbo (which was my initial understanding). I'm sorry if this is confusing -- we plan on attaching/detaching the database on a regular basis, but we can prepare it initially. Thanks everyone for your help. Ken On Nov 2, 3:01 am, "Tibor Karaszi" [quoted text, click to view] <tibor_please.no.email_kara...@hotmail.nomail.com> wrote:
> Here's what I think is going on: > > Each database has a users table, exposed as sys.database_principals. Here you can see the SID of the > owner of the database (dbo, see the sid column). Detach this on one instance, and you will have some > sid for a login on that instance. > > Now, attach into another instance, and the size from above doesn't exist on the new instance. You > have an orphaned owner. Seen from the database perspective. > > However, in master, there's a table for each database, exposed as sys.databases. There's a sid > stored here as well, owner_sid. When you attach, the information in sys.databases (in master) is set > to the login who attaches the database. But the information *inside* the database isn't set > accordingly. Hence the inconsistency in the SSMS dialogs. > > If you now change the database owner (sp_change_dbowner), the sid *inside* the database is set > correctly. > > So the solution seems to be to set the owner properly after attaching the database. > > -- > Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://www.solidqualitylearning.com/ > > <kenandco...@gmail.com> wrote in messagenews:1162278829.923464.274290@m7g2000cwm.googlegroups.com... > > >I am confused about database ownership when using sp_attach_db. I want > > to have a non-sysadmin user be able to attach and detach a database, as > > well as be the owner of the database it attaches. > > > In SQL Server 2005, I've created a login called "Maint". I have given > > this user a Server Role of "dbcreator". When I log in as Maint, I can > > attach a database, and it shows "Maint" as being the owner of the > > database. But when I go "USE Maint_db" it says Maint doesn't have > > access: > > > Msg 916, Level 14, State 1, Line 1 > > The server principal "Maint" is not able to access the database > > "Maint_DB" under the current security context. > > > Then I go to the Login Properties for the Maint login, and under user > > mappings everything is blank. I expected to see Maint_DB have a user of > > Maint with a Default Schema of dbo. > > > If I go to the Maint_DB properties, it does show Maint as the owner. > > > What am I missing? If I change the database owner to sa, and then back > > to Maint, everything works as I would expect: Maint is the true owner > > of the database and can access all objects in it. > > > Can anyone point me to what I am doing wrong? I would like to be able > > to attach a database and set it's owner to Maint from a stored > > procedure, while giving Maint only the minimal required access. I tried > > running it as a sysadmin, and that sure made things work well. :) > > > Thank you in advance. > > > Ken
Your workaround seems to cut it, especially as this is in a controlled environment (you don't attach
this to just any SQL Server instance out there). Two things that might or might not be helpful: Google for sp_help_revlogin. This generates a script of your existing logins so you can re-create a login having the correct SID on the destination server. CREATE LGOIN has a SID option where you can specify the SID for the login you are about to create. (This feature is most probably what above routine is using in the end.) -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ [quoted text, click to view] <kenandcorey@gmail.com> wrote in message
news:1162487934.334323.176800@b28g2000cwb.googlegroups.com... > Hello Tibor, > > Thank you for taking the time to reply. Your suggestions helped me to > pin down what I think the problem is. The show-stopper is that to run > sp_change_dbowner you need to be able to switch to the database (USE > databasename). Unfortunately I can't do this when logged in as the > Maint user, as he doesn't even exist in the sys.database_principals > table for the Maint_DB database. > > So to work around this, I plan on making sure the Maint user exists in > the Maint_DB database before I attach it. Then when I do attach it > using the Maint user, it WILL have proper access to the database. > Since I'll always be attaching it to the same database server, the SIDs > shouldn't get out of sync -- at least that is my understanding. > > I plan to do the following: > - As a sysadmin > - Attach the database > - make Maint the owner > - Detach the database > > Now, as the Maint user, whenever I attach the database I am already the > dbo and everything is good. My confusion was in the fact that the > login attaching the database does not automatically become the dbo > (which was my initial understanding). I'm sorry if this is confusing > -- we plan on attaching/detaching the database on a regular basis, but > we can prepare it initially. > > Thanks everyone for your help. > > Ken > > > On Nov 2, 3:01 am, "Tibor Karaszi" > <tibor_please.no.email_kara...@hotmail.nomail.com> wrote: >> Here's what I think is going on: >> >> Each database has a users table, exposed as sys.database_principals. Here you can see the SID of >> the >> owner of the database (dbo, see the sid column). Detach this on one instance, and you will have >> some >> sid for a login on that instance. >> >> Now, attach into another instance, and the size from above doesn't exist on the new instance. You >> have an orphaned owner. Seen from the database perspective. >> >> However, in master, there's a table for each database, exposed as sys.databases. There's a sid >> stored here as well, owner_sid. When you attach, the information in sys.databases (in master) is >> set >> to the login who attaches the database. But the information *inside* the database isn't set >> accordingly. Hence the inconsistency in the SSMS dialogs. >> >> If you now change the database owner (sp_change_dbowner), the sid *inside* the database is set >> correctly. >> >> So the solution seems to be to set the owner properly after attaching the database. >> >> -- >> Tibor Karaszi, SQL Server >> MVPhttp://www.karaszi.com/sqlserver/default.asphttp://www.solidqualitylearning.com/ >> >> <kenandco...@gmail.com> wrote in >> messagenews:1162278829.923464.274290@m7g2000cwm.googlegroups.com... >> >> >I am confused about database ownership when using sp_attach_db. I want >> > to have a non-sysadmin user be able to attach and detach a database, as >> > well as be the owner of the database it attaches. >> >> > In SQL Server 2005, I've created a login called "Maint". I have given >> > this user a Server Role of "dbcreator". When I log in as Maint, I can >> > attach a database, and it shows "Maint" as being the owner of the >> > database. But when I go "USE Maint_db" it says Maint doesn't have >> > access: >> >> > Msg 916, Level 14, State 1, Line 1 >> > The server principal "Maint" is not able to access the database >> > "Maint_DB" under the current security context. >> >> > Then I go to the Login Properties for the Maint login, and under user >> > mappings everything is blank. I expected to see Maint_DB have a user of >> > Maint with a Default Schema of dbo. >> >> > If I go to the Maint_DB properties, it does show Maint as the owner. >> >> > What am I missing? If I change the database owner to sa, and then back >> > to Maint, everything works as I would expect: Maint is the true owner >> > of the database and can access all objects in it. >> >> > Can anyone point me to what I am doing wrong? I would like to be able >> > to attach a database and set it's owner to Maint from a stored >> > procedure, while giving Maint only the minimal required access. I tried >> > running it as a sysadmin, and that sure made things work well. :) >> >> > Thank you in advance. >> >> > Ken >
Don't see what you're looking for? Try a search.
|
January 2003
March 2003
April 2003
May 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
April 2008
August 2008
| home · blog · groups | Questions? Comments? Contact the dn |