2

I needed a custom SQL Server role which has readonly access to all databases, regardless of whether a database is existing or is newly created.

The role is not a problem. The problem is that the role will not be applied to existing databases. If I create a new database, the role is applied and does working as I expected.

I assume there will be some system stored procedure that will be run at the moment, a new database is created.

Is there any possibility to apply the custom server role afterwards to databases which are existed before i created the custom server role?

I am using SQL Server 2012.

Thanks in advance

|improve this question
  • 1
    Please provide the syntax of the commands you are executing to create/manipulate the role. – Mike Fal Aug 12 '14 at 20:27
  • I can't because I used SSMS for this. – Peter Bucher Aug 13 '14 at 11:17
  • Go through the same steps as before and instead of hitting "OK" hit "Script". – Kenneth Fisher Aug 13 '14 at 13:51
0

I suspect that you are not actually creating a SERVER ROLE but in fact are creating a DATABASE ROLE in the model database. I looked under server roles for a bit and could find nothing that would grant read access to a database (old or new). I can see ALTER ANY DATABASE, CREATE ANY DATABASE and VIEW ANY DATABASE but nothing about read/write of the tables.

If what I suspect is correct and you are creating a role in the model database then it would have exactly the effect you are discussing. You would see the role show up in all new databases but not the old ones. Unfortunately the only way around this is to create the role in each of the existing databases. Fortunately this is fairly easy.

First thing would be to generate a script that creates the role either by using a scripting tool, creating it in the GUI and hitting the script button, or using the generate scripts option (right click on the database).

Once you have the script for the role you can either manually run it on each of your existing databases (probably ok if you only have <50 or so) or you can generate some dynamic SQL to run the script for you.

|improve this answer
  • Hi Kenneth. It is not what you suspecting. I have a custom server role, not a database role. There are "View any database / View any definition and View server state". OK then I have to add the server role manually in any existing database. – Peter Bucher Aug 12 '14 at 9:11
  • To the best of my understanding none of those will grant you "read only" on a database. You would be able to view the definition of any stored procedure in any database for example but not the contents of a table. Also this would happen without adding the role to a database. Can you explain how you are "adding" the role to the databases? – Kenneth Fisher Aug 12 '14 at 12:18
  • I added the role to a user, which has usermappings to all databases. The point is, that the server role adds to every new database the db_datareader role. I dont know why, but this works. From all tables (except system ones) i only have to read the definion for scripting it out. – Peter Bucher Aug 12 '14 at 15:34
  • Unless I'm very confused you can't actually add a user to a server role. You can add a login (server level principal) but not a user (database principal). Can you explain how you are doing this? FYI db_datareader is a fixed database role and exists on all databases. – Kenneth Fisher Aug 12 '14 at 18:42
  • I meant a server level principal. db_datareader: I needed a server role, NOT a database role. Because it should apply for all databases without adding manually. – Peter Bucher Aug 13 '14 at 11:12
0

There came no other solution up than manually make Usermappings to all databases existing before the server role does. For upcoming databases, the role will automatically applied.

|improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.