October 3, 2011 at 1:24 pm
I'm having some security related issues, and cannot google my way to a solution ... I'm hoping someone here will help.
I need to be able to create a user that can "Create/Attach" databases, and then have full DBOwner access to that newly created/attached database. I would think this would be a no-brainer (why wouldn't a creater of a DB be in the dbowner role by default?), but I'm clearly missing something.
I create a user. I then "GRANT CREATE ANY DATABASE TO" that user.
This user can now successfully create the database with a CREATE DATABASE FOR ATTACH. Works fine.
... until the user tries to do anything in the database. I get the error:
"The server principal "MyTestUser" is not able to access the database "myTestDatabase" under the current security context."
So my question is, what am I missing? What SQL Script or configuration can I run that will give this user the ability to create/attach databases, and then (without any separate intermediate steps, since this is going to be a running script -- i.e. I can't have some sysadmin step in and grant the user DBO permissions on the new database that was just created) have full access to the newly created database?
Honestly, this would seem to me to be something easy/obvious, but I've been banging my head against this for a while now. We have strong reasons to not want dbcreator or dbowner roles on this user. The user should be able to create a new database, and should then be full owner of that database from that point on (and any other database that user creates and ONLY for databases that user creates).
Help? Thanks in advance...
October 3, 2011 at 1:38 pm
I don't believe this can be done.
http://msdn.microsoft.com/en-us/library/ms189128.aspx
Thanks,
Jared
Jared
CE - Microsoft
October 3, 2011 at 1:44 pm
That seems to be referring to physical NTFS file security. I'm just refering to getting a login that has created a database for attach, to be able to be a user in that database with dbo permissions.
This would seem to be an obvious need. I can't imagine it's not possible. If a login can create a given database, what sense does it make to have them completely locked out of that database? That doesn't make any sense at all.
I'm trying to write a script that creates/attaches a base database, and then installs/upgrades it from a script. This too would seem to be a rather common scenario (even without the attach, the need to be able to create a database and then populate it with tables, views, etc). Obviously this can be done with a login with the sysadmin role, but we really need to lock this down a bit so that a given login only has permissions to the database they actually create.
I'm finding myself a bit frustrated that something that seems so straight forward is stumping me. I'll be doubly so if it's not even possible.
October 3, 2011 at 1:50 pm
paul take a look at this thread from a while ago
http://www.sqlservercentral.com/Forums/Topic1115027-359-1.aspx
in that thread, I slapped together an example of a proc that executed as dbo,and did the following:
created(or found) a database based on a parameter passed.
created some specific roles in that new database.
added the calling user to one or two of those specific roles.
it's using dynamic SQL, but it sounds pretty close to what you are looking for.
Lowell
October 3, 2011 at 1:51 pm
Hmm... There has to be another permission for this user that is not granted. Did you revoke any permissions?
Thanks,
Jared
Jared
CE - Microsoft
October 3, 2011 at 1:55 pm
I did not revoke any permissions. There is no user. There is only a login. And this login has been granted the CREATE DATABASE permission.
This is what I want:
- This login should be able to create a database (it can)
- It then needs to be able to add itself as a user to the database (I can't figure this part out)
- And then it needs to be free to add/alter/drop any and all schema, do inserts/updates/delete to any table, etc. In other words, be the db_owner for that newly created database. (can't figure this part out either)
October 3, 2011 at 2:14 pm
Just did some testings in SQL 2008 with SQL logins...
1. The user with CREATE DB permissions would be automatically granted as DB owner ( just like what the OP wants) if it creates DB with the normal way
i.e., not through FOR ATTACH
create database test
ON PRIMARY
( NAME = N'test', FILENAME = N'D:\SQLDATA\test1.mdf' , SIZE = 5120KB , FILEGROWTH = 10%)
LOG ON
( NAME = N'test_log', FILENAME = N'D:\SQLDATA\test_log1.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
2. The user can not have any access permissions if it creates the DB by using FOR ATTACH
October 3, 2011 at 2:28 pm
DBA7: It's not "cannot", it's "does not".
Paul,
you are confusing the server level permissions with database permissions. Being able to create a database does not imply permission to get inside it. It implies permission to update system tables to add a database to the server instance.
If you want to grant the user rights inside the database, they would need some level of security permissions for their login that would allow them to map their login to a user in the database (or add the user).
Lowell's post has a link to a way to do this, though you might need to grant additional server level permissions.
October 3, 2011 at 2:34 pm
Steve Jones - SSC Editor (10/3/2011)
DBA7: It's not "cannot", it's "does not".Paul,
you are confusing the server level permissions with database permissions. Being able to create a database does not imply permission to get inside it. It implies permission to update system tables to add a database to the server instance.
If you want to grant the user rights inside the database, they would need some level of security permissions for their login that would allow them to map their login to a user in the database (or add the user).
Lowell's post has a link to a way to do this, though you might need to grant additional server level permissions.
Steve,
Am I also right is assuming that the permissions to this database are already defined in the mdf file that is being attached? i.e. if the database owner of the database before it was detached was the person who is now attaching it, it would be working as Paul wishes?
Thanks,
Jared
Jared
CE - Microsoft
October 3, 2011 at 2:48 pm
I don't think I'm confusing the two. I just don't see ANY utility in a login being able to create databases (and be the "owner" according to the properties) and yet have no permissions in that database. That just seems pointless and even broken to me.
Unfortunately I'm constrained to use ATTACH for now. We don't have a fully scripted database just yet.
Where I'm really confused is that if I create a login with sysadmin privileges, I don't need to create any users or anything. I have full access to the database, and can do anything I want, without having that database explicitly listed in any User Mapping.
Basically, I cannot understand why some login that is listed as the "owner" has no permissions to a database, and can't seem to get them without requiring a sysadmin to come over and create a user for that login, and then granting that user "dbo" permissions. (in fact, I'm still struggling on doing THAT).
What I honestly want, is a login that can 1) create the database FOR ATTACH, and then 2) run all the SQL scripts against that database necessary to create/alter/drop any schema, and insert/update/delete any data it wants... without having to create a specific USER in that database.
So far, I'm gathering that the only way to do that is by creating a LOGIN with the db_sysadmin ROLE. Unfortunately, that's not acceptable.
I want a login with the "CREATE DATABASE" permission to be able to do its work in the database that it just created and is currently listed as the owner of.
I double-checked, and when this TestUser I'm using CREATES the database FOR ATTACH, when I go in and look at the database properties, "TestUser" is definitely listed as the owner. I still think it makes no sense what-so-ever that the Owner not only has no permissions, but can't even GET permissions without a sysadmin's intervention.
I've looked over Lowell's stored procedure, and can't really wrap my head around it and how it relates to what I want to do. I'm feeling pretty stupid about this stuff all of a sudden, that I thought I understood. I guess the main confusion is how I can create a user with sysadmin role that doesn't need an associated user in the database to do anything... and that's exactly the same thing I want in this.
So I'm wondering about how to modify that stored procedure to do what I need... which does not involve creating (that's already done in a CREATE FOR ATTACH statement in my script). I really just want this user (I would like to not have to require a hard-coded user here) that is currently running, that created the database, and who is listed as the databases owner... to actually BE the owner of this database. The problem I have is that because the creator can't see into the database at all, I can't bootstrap the creator. I'm also not certain that requiring customers to add this stored procedure to their master database before they can successfully install or upgrade is acceptable either.
Once again, I'm just nearly incredulous that a db-creator can ever not be a "db-owner". I can't even imagine a use-case there that is valid. At least not one more common than the creator IS the owner. Are we sure this isn't a bug? Because it sure feels and smells like a huge bug to me. To have a login listed as "owner" in the DB properties, but that login has no permissions to the database at all... doesn't seem right.
I feel I'm still being thick-headed and just missing something.
October 3, 2011 at 2:53 pm
I think you are witnessing the reason that companies hire DBA's 🙂 As a DBA I routinely create databases and add the proper permissions. The reason we do not let developers do this is because someone has to be responsible for the space and what resources are being used. Also, how many databases need to be managed. There is a good reason for it, that reason may just not apply to your business case.
Thanks,
Jared
Jared
CE - Microsoft
October 3, 2011 at 3:04 pm
If I look at the database properties after the CREATE DATABASE FOR ATTACH, the "Owner" is listed as the login that ran the CREATE DATABASE FOR ATTACH. However, that login has no permissions to the database at all.
October 3, 2011 at 3:10 pm
Well, in this case, we're trying to help out DBAs! 🙂
DBAs don't like having to have the "sa" password handed out in order to do upgrades and installs. Now, we can get around that trivially by just telling them to create a user with the sysadmin role, and just use that. But... that doesn't help because it's "just as bad" as giving out the sa password.
So we're trying to create something where the DBA can create one account, and that account can then be used by install/upgrade but NOT have full sysadmin accounts. Only it's turning out to be impossible, which is extremely frustrating. In fact, it seems to defeat the whole purpose. We're basically being FORCED to use sa/password or full sysadmin priviliges, simply because something as obvious and intuitive as "the DB creator is the freakin' OWNER" doesn't work as it (to me, "obviously") should.
If, however, I'm correct in reading that this does work as long as you don't do an attach... then perhaps trying to push the project to get our database deployable in a fully scripted way is the only solution to this. Which would suck, but at least it's a possibility. Frankly, until proven otherwise, I think I'm just going to consider the CREATE DATABASE FOR ATTACH functionality to be *broken*. Because it just seems completely broken to me.
October 3, 2011 at 3:35 pm
Just curious... Why aren't the DBAs doing the upgrades and installs? Unless you are not talking about SQL Server itself... I guess I am just confused. You have to understand though, that this is not "broken." It is intended as just being allowed to attach a database does not always give you the right to change its schema.
Thanks,
Jared
Jared
CE - Microsoft
October 3, 2011 at 3:46 pm
This has nothing to do with installing or upgrading SQL Servers. It's our application.
Right now our application basically requires the sa user/password to install and upgrade. Mostly because we're doing an attach-db as part of the process. We're just trying to eliminate this over-broad requirement. In order to do that, we need to be able to attach the DB and then work inside it in the same sesson/script, without requiring a sys admin to pause the process, muck around manually, and then resume the process. We want it all to be automated.
And yes, I think not being able to automate this is broken. An "owner" should be an "owner"... not a "completely locked out user with no permissions just because the database was created with attach even though its actually listed as the Owner". Sigh. Been banging my head on this for days and am just sort of incredulous that something this obviously useful and necessary is ... something nobody has ever thought of or tried before and is currently impossible.
Even if there were some odd work around... like, I could create a user in the database prior to detatch. If a sysadmin created a login by that same name prior to installing our product (which does the attach), would they be automatically linked by name? Is there some other permission I have to grant to this login besides CREATE DATABASE that would be helpful, without opening the login up to the entire server or other databases?
Isn't "creating a database and then doing stuff to it" the primary use case for any database? Why would that one use case be impossible? This is why I'm frustrated... I'm confused as to why the one thing I need to do isn't allowed. It doesn't make any sense to me at all. I cannot even imagine a use-case where "I created the database, and I am the owner, but I have no permissions to it and can't get them without a system admin interveining manually" makes any sense at all, or is even remotely desirable. It certainly makes scripting/automating database installs impossible unless you're willing to open the entire DB up via system admin logins.
Viewing 15 posts - 1 through 15 (of 36 total)
You must be logged in to reply to this topic. Login to reply