October 3, 2011 at 3:49 pm
weird posting glitch/double-post. Deleted.
October 3, 2011 at 3:52 pm
It is intended as just being allowed to attach a database does not always give you the right to change its schema.
Okay, but allowing for that use case shouldn't disallow for the other (to me, far likelier) cause where I need to attach a model database and then update/tweak it for the specific installation.
I'm just trying to figure out what the magic incantations required for doing this are. What permissions have to be set up in advance, so that a login can attach a database, and modify it, and use it.
October 3, 2011 at 4:04 pm
I am spoilt by always having sysadmin rights so things like this are never a problem to me, so I might be wrong but it might be file level permissions that are preventing the user accessing the database, rather than permissions in SQL.
As a test after the database is attached if you could go to the files and grant everyone access to them and then see if the user can access the database, Bad practice I know but just for the purposes of this test.
Try also using your dbcreator login to create a database from scratch (not with the attach option) or restoring a backup and seeing if the user can then access the database, I would expect them to be able to.
I applaud you for trying to go the route of least privileges but if this is an app you are selling to outside customers someone with sysadmin rights would normally install the database. They would expect to receive a script that creates and then populates the database as well rather than a detached database.
going the route of a .bak file to restore or a script that creates the database from scratch may be a better long term solution for you.
---------------------------------------------------------------------
October 3, 2011 at 4:17 pm
I am spoiled by the same thing, which is why I'm having such trouble here.
Okay, I did your test, and you were right. Adding Everyone Full Control to the mdf/ldf files made the error go away. Which is really perplexing to me, and now I have to appologize to one of the first responders. The error messages were completely misleading me as to the root of the problem.
So... there's still a problem, but I think this one might be more solvable. At least I hope.
And as for your other comments: This is an ancient system. Yes, we know a fully scripted database is preferable, and fully intend to get there eventually. We were looking for something more "short term". And currently, the same user entered for the creation/upgrade is the same user stuck into connection strings in web.config files, so we're just trying to get away from using the sa account or sysadmin level accounts. The nature of our product is such that schema can be created/updated while it runs.
So, now I need to figure out how to modify the NTFS permissions on the files (either prior to or post attach) such that things work, but the files aren't "everyone full control" 🙂
I'll go back and re-read the first linked discussion... but I'll probably have more questions 🙂
Thanks!
October 3, 2011 at 4:25 pm
the detached files must have come from somewhere, no chance they cannot be replaced by a database backup?
---------------------------------------------------------------------
October 3, 2011 at 6:16 pm
george sibbald (10/3/2011)
the detached files must have come from somewhere, no chance they cannot be replaced by a database backup?
This is a good idea. Can the user simply create the database and restore from a database backup? They should have permissions to do that and then they will have all permissions since they actually created the mdf file? From an application standpoint, you would replace your mdf file with a bak and simply restore it. Then, you will always have the original file still there in case of a "restore to factory default" 🙂
Thanks,
Jared
Jared
CE - Microsoft
October 4, 2011 at 9:00 am
Well, the entire point is that we're trying to automate this by scripting it or doing it in installshield, so having the customer manually restore the backup isn't really an option. But I will try and see if the restore from backup works any better than create-for-attach, though given the linked page earlier, there are still some security issues likely.
Honestly, I still don't understand why an account that creates-for-attach can't have access to the DB... why the current implementation is not "broken". I just can't conceive of when this is ever the right thing to do. I realize now that mucking with the NTFS permissions in the case of SQL Server login authorization (not Windows) is documented behavior, but it just strikes me as a needless impediment to getting anything to work correctly, as-expected. It's completely unintuitive. Especially if a 'restore from backup' actually gets around the behavior (which remains to be seen).
I'll report back.
October 4, 2011 at 9:09 am
You will be able to script the backup just as you script the attach. Simply create the database first with no tables, then restore the backup with a script:
USE master;
GO
CREATE DATABASE dbName
ON
( NAME = dbName_dat,
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\dbName.mdf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5 )
LOG ON
( NAME = dbName_log,
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\dbName.ldf',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB ) ;
GO
----Make Database to single user Mode
ALTER DATABASE dbName
SET SINGLE_USER WITH
ROLLBACK IMMEDIATE
----Restore Database
RESTORE DATABASE dbName
FROM DISK = 'D:\dbName_backup.bak'
ALTER DATABASE dbName SET MULTI_USER
GO
This should get you started. Also, check out this link: http://blog.sqlauthority.com/2007/02/25/sql-server-restore-database-backup-using-sql-script-t-sql/
EDIT: Change the file paths as needed.
Thanks,
Jared
Jared
CE - Microsoft
October 4, 2011 at 9:15 am
you don't need to create a database first before restoring.
---------------------------------------------------------------------
October 4, 2011 at 9:18 am
george sibbald (10/4/2011)
you don't need to create a database first before restoring.
Ooh! Good point... However, I'm interested to see if there is a difference in permissions granted to this user depending on whether the db is created first or not.
Thanks,
Jared
Jared
CE - Microsoft
October 4, 2011 at 12:26 pm
No dice.
The "restore" process mucks with the NTFS security descripter just like the CREATE DATABASE FOR ATTACH does.
Got a great script working to restore from backup, and once the DB is restored... the user can no longer access the database. Makes no difference whether I create the database first or not.
This is TRULY broken/bugged. I just can't fathom how this makes any sense. If I create the database, and then script everything into it, everything seems to be fine (if only that were a near-term option). If I restore or attach the database instead of scripting it, I'm locked out with no recourse at all.
Ugh. There has GOT to be a way to do this. I'm at my wit's end here.
October 4, 2011 at 12:44 pm
See below...
Thanks,
Jared
Jared
CE - Microsoft
October 4, 2011 at 12:55 pm
See below...
Thanks,
Jared
Jared
CE - Microsoft
October 4, 2011 at 1:20 pm
Ok, I just did some testing. I believe that the login you are using to attach/restore the database was never given access to the original database 🙂
Attach/restore the file somewhere.
Login as sa and map the login you are using to attach to be db_owner of the attached database.
Detach/backup the modified db
Attach or restore using your login for this
The problem is that on the properties of the database, it shows the login that you used to create it. However, that login is NOT a user on the attached/restored database (this is stored with the backup or .mdf file and replaces any users that were created on your new db). So that user has to be added to the db file or db backup first before attaching or restoring. This is why creating the database without attach works fine. The user that creates the db is added as a database user. But with the attach or restore, that user gets overwritten with the users that were on the original.
I did this, and it worked fine for me.
Thanks,
Jared
Jared
CE - Microsoft
October 4, 2011 at 1:45 pm
jared-709193 (10/4/2011)
I think that part of the issue here is that attaching or restoring is bringing in the database users with it. When you do this from 1 instance to another, the users become essentially "unlinked." This is because each instance has a unique identifier associated with each login and they do not match from instance to instance. When you restore the backup, try running this script:
USE dbName;GO
EXEC sp_change_users_login 'Auto_Fix', 'loginName' Where loginName is the login that you are using. This fixes the link between the login and the database. The database already knows that this user is the creator, but maybe it cannot link it to the login. I have to run this whenever I restore a database from TestServerA to TestServerB on the database owner. Maybe this is all you need?
Thanks,
Jared
That sounds awesome, but the primary issue is that "Use dbname" at the start... the user in question cannot do this, as they get this error: "The server principal "TestUser" is not able to access the database "TestUserTest" under the current security context".
Let me also be clear that there is no "user" in this database associated wtih the login... just like if the login were a sysadmin (where everything works fine). I've also checked the ACLs on the actual MDF files in the file system, and they look perfectly normal, like every other database.
Yesterday, I added "everyone full control" to the ACL and suddenly things worked. Today? Still broken. (the only difference was attach vs. restore, but I'm not sure that explains it... I'm starting to think I'm going crazy here 🙂 ).
I seem to be stuck in an endless loop of confusion :-/
So... where does this leave me? The only thing I've gotten to work is, after the Restore, I've had to log in with a sysadmin user, and run the following commands:
USE TestDatabase
CREATE USER TestUser FROM LOGIN TestUser
EXEC sp_addrolemember 'db_owner', 'TestUser'
This seems to me to be wholely redundant, since "TestUser" is already listed as the "owner" of the DB in the DB properties, and in fact, Created the Database and Restored it. "TestUser" is a login that has the CREATE DATABASE permission, and is otherwise just a regular plain login.
Since that "manually have a sysadmin intervene" isn't a viable option, I may have to give up on this... unless anyone can find some magical incantation, combination of permissions, and steps, to get around what seems to me to be a clearly broken system.
BTW, I understand that the "Users" in the restoring/attaching database are essentially orphaned. We have a script that runs that cleans up the orphaned users, and creates others required by the application, so that's not a problem. The problem is the chicken-and-egg problem of creating a non-sysadmin database Login that can restore/attach the new database (in effect, creating it) and then actually USE the database and run these scripts to fix up other issues. Basically the 'install' is attempting to start from a well known base, and tweak that to fit the installation parameters (modifying schema and data as required).
And to answer another question asked up there: the application is installed at client sites, each with their own SQL Servers, though for some resellers, they might have multiple customers on the same SQL Server... which is one of the factors driving this need to not have to hand out sysadmin privileges just to get installs and upgrades to work.
Thanks for all the help and suggestions, but I'm starting to think the ONLY solution is to manually script off all the tables and data (which would run significantly more slowly than an attatch/restore... orders of magnitude slower as far as I can tell). This can be done, but it's not a small project and has its own issues (we preload hundreds of tables with lots of data, for starters, so it's non-trivial, and many of the tables have binary data (BLOB columns) so simple text scripting of INSERTS is a pain to get right... which leads to a tangential question: Are there better command-line/scriptable options than using BCP for each table? I suppose I should start a new thread for that...)
Thanks again for any comments, brain-storms, suggestions, or commiseration!
Viewing 15 posts - 16 through 30 (of 36 total)
You must be logged in to reply to this topic. Login to reply