January 5, 2016 at 1:49 pm
hi - is there way to create an user login that has access (read/write) to all databases except one database?
please assume the restricted database name is "known".
ex: john should have access to all databases both current and "future" except the known, restricted database. so when a new database is created, john would have access to that database automatically.
thanks for your help.
January 5, 2016 at 2:11 pm
Not tested but I guess this would work:
1. Add the user to the model database. New databases are created as a copy of model so I expect the user to immediately have access.
2. Remove the user from the excepted database(s).
Try it and let us know if it works!
January 5, 2016 at 3:29 pm
Thanks for your reply. Tried it but wont work. May be few more things needed? Please see below for my steps.
Steps:
0. Logged in as windows user (admin)
1. Create a login (testuser) under Security -> Logins
2. Picked one default database (say Adhoc)
3. Added the user to this (Adhoc) database
4. Executed this on Adhoc: GRANT SELECT, INSERT, UPDATE, DELETE TO testuser ;
5. Logged off and logged in as testuser
6. Can see all the tables etc. in Adhoc just fine but cant execute things like: select * into adhoc.dbo.table_copy from adhoc.dbo.table
7. Logged off and logged in as windows user (admin)
8. Added "testuser" in System Databases - > model -> Security -> Users -> Create User -> User = testuser, login = testuser, Default Schema = dbo
9. Created a new database "testdb1". As you pointed out, it inherited the "testuser" from model db
10. Logged off and logged in as "testuser"
11. Able to navigate through but cant create tables etc. in "testdb1"
Questions:
1. Looks like your method would only work for all go-forward/new databases. What are my options to grant access to "testuser" for all existing databases?
2. How can the "testuser" create tables, drop views do anything on "testdb1" except "dropping the database"
Please advise.
Thanks much1
January 5, 2016 at 3:52 pm
To give a user generic rights in a database, the easiest solution is to add them to a fixed database role. E.g. database_reader can read all tables, database_writer can modify data, etc. The fixed database roles are described in Books Online.
Adding a user to model and assigning rights there was my solution for giving the user access to future databases. I had overlooked the existing databases. Some things you can do for this are:
* Manually go through the databases (probably fastest if it's just a few)
* Script the SQL to assign the rights, then use the undocumented but fairly well-described sp_MSForEachDb to submit that script to each database. (Note that there are some known glitches with this stored procedure, and that some people have posted better replacement versions online - e.g. here: http://sqlblog.com/blogs/aaron_bertrand/archive/2010/12/29/a-more-reliable-and-more-flexible-sp-msforeachdb.aspx
January 5, 2016 at 3:56 pm
murthyvs (1/5/2016)
Thanks for your reply. Tried it but wont work. May be few more things needed? Please see below for my steps.Steps:
0. Logged in as windows user (admin)
1. Create a login (testuser) under Security -> Logins
2. Picked one default database (say Adhoc)
3. Added the user to this (Adhoc) database
4. Executed this on Adhoc: GRANT SELECT, INSERT, UPDATE, DELETE TO testuser ;
5. Logged off and logged in as testuser
6. Can see all the tables etc. in Adhoc just fine but cant execute things like: select * into adhoc.dbo.table_copy from adhoc.dbo.table
7. Logged off and logged in as windows user (admin)
8. Added "testuser" in System Databases - > model -> Security -> Users -> Create User -> User = testuser, login = testuser, Default Schema = dbo
9. Created a new database "testdb1". As you pointed out, it inherited the "testuser" from model db
10. Logged off and logged in as "testuser"
11. Able to navigate through but cant create tables etc. in "testdb1"
Questions:
1. Looks like your method would only work for all go-forward/new databases. What are my options to grant access to "testuser" for all existing databases?
2. How can the "testuser" create tables, drop views do anything on "testdb1" except "dropping the database"
Please advise.
Thanks much1
In step 4, you granted SELECT, INSERT, UPDATE and DELETE. In step 6, you tried to create a table and it didn't work because the user doesn't have permission to create a table. You may need to look at the database roles db_datareader, db_datawriter and db_ddladmin instead of the individual permissions. You can read about the permissions granted by each database role at https://msdn.microsoft.com/en-us/library/ms189121%28v=sql.110%29.aspx.
Hugo's approach is definitely for databases going forward. When you create a new database, it's created from the model database, so adding a user there will result in a user existing in the new database you create. For existing databases, you'll have to create a user mapped to the login in each database and apply the appropriate permissions. Whether you do this by clicking your way through or programmatically is up to you, but I would write a script to generate the SQL for me. Then again, I don't like clicking much of anything when I can use SQL. 😉
The first step is to figure out what permissions you want to assign in each database. Get it done and tested, but keep track of them along the way. After that, writing the SQL to do the work should be pretty straightforward.
January 6, 2016 at 7:10 am
Thanks for your input. I tried adding "database roles db_datareader, db_datawriter and db_ddladmin instead of the individual permissions" to the adhoc database for the testuser. I could login just fine and navigate - no problems. But I cannot see any tables, views, sps etc. It appears as it is a brand new database. Note - when I added those individual permissions, I can see tables, views etc.
Yes - I will have to figure a way to apply the permissions to individual databases. I will look into Hugo's suggestions.
As far as what permissions the user would need: basically anything except dropping the database. Not sure if such thing exists.
@hugo, @Ed - Any help greatly appreciated.
January 6, 2016 at 8:32 am
murthyvs (1/6/2016)
Thanks for your input. I tried adding "database roles db_datareader, db_datawriter and db_ddladmin instead of the individual permissions" to the adhoc database for the testuser. I could login just fine and navigate - no problems. But I cannot see any tables, views, sps etc. It appears as it is a brand new database. Note - when I added those individual permissions, I can see tables, views etc.Yes - I will have to figure a way to apply the permissions to individual databases. I will look into Hugo's suggestions.
As far as what permissions the user would need: basically anything except dropping the database. Not sure if such thing exists.
@hugo, @Ed - Any help greatly appreciated.
I'm afraid I don't really understand what's going wrong.
I just tested this - gave an existing test user access to one of my existing databases, in the roles db_datareader, db_datawriter, and db_ddladmin. Then used EXECUTE AS to simulate their security context, used USE to switch to the test database and executed a SELECT statement. Worked fine. As an extra test, I then removed the test used from those three roles and repeated the experiment, now I coult access the database but not read any data.
What are the exact steps you take to try this?
January 6, 2016 at 10:28 am
Think its my error. I was selecting db_datareader, db_datawriter, db_ddladmin under Owned Schemas section. I selected the same ones in Membership and its working as expected now. Thanks for your help.
January 6, 2016 at 10:33 am
murthyvs (1/6/2016)
Think its my error. I was selecting db_datareader, db_datawriter, db_ddladmin under Owned Schemas section. I selected the same ones in Membership and its working as expected now. Thanks for your help.
Ah yes...one of the glories of using the GUI. The owned schemas looks similar to the role membership. When we moved to SQL 2012, it took me a while to get used to it myself.
I'm glad you got it figured out. Thanks for the feedback.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply