Understanding SQL Server fixed database roles

By:   |   Updated: 2009-12-14   |   Comments (18)   |   Related: > Security


Problem

I know there are fixed database roles that come with SQL Server. How do I best use them within my installations? What should I watch out for? In this tip we will cover each of the database roles and recommendations on when to and when not to use them.

Solution

Within each database, SQL Server does have fixed database roles, ones which are standard and included in every database. These differ from the database roles you can create and use yourself in that they have pre-assigned permissions. The fixed database roles are:

  • db_owner
  • db_securityadmin
  • db_accessadmin
  • db_backupoperator
  • db_ddladmin
  • db_datareader
  • db_datawriter
  • db_denydatareader
  • db_denydatawriter

Like with the server roles, let's look at each in turn.

db_owner

We'll start with the biggest role: db_owner. A member of the db_owner role can do anything inside the database. Now there is a difference between a member of the db_owner role and the dbo user. That difference is that if someone maps into the database as the dbo user, that person bypasses all security checks. An example of this is anyone who is a member of the sysadmin fixed server role. They map in as dbo. And as a result, they don't receive security checks.

If a user is not dbo but is a member of the db_owner role, it does receive a security check. Of course, unless you've explicitly used DENY to block access, that user can do what he or she wants. The DENY stops them cold (it does not stop dbo). However, a member of the db_owner role could remove the DENY, so effectively that person can do anything, even if you put roadblocks in place. Therefore, the db_owner role should be given out only when necessary. Some applications will require it, which is a headache, but rarely will actual people need it (unless the application is connecting using their credentials). So you should be able to keep a reasonable tight control over this role. Like sysadmin, which is returned as a member of every fixed server role if you use the IS_SRVROLEMEMBER() function, if you query for someone who is a member of the db_owner role to determine if that user is a member of any other fixed database role, it will return true, even if the user is not explicitly a member of that role. For instance, a user who is a member of db_owner but who is not a member of db_securityadmin will still return a 1 if you execute the following query:

SELECT IS_MEMBER('db_securityadmin');

Things to remember:

  • The db_owner role allows a user to do anything within the database.
  • DBAs who are already members of the sysadmin fixed server role come in as dbo and don't need this role explicitly granted to them.
  • Normal users should not be a member of this role.
  • Applications might require their user account to be a member of this role.

db_securityadmin

Like the securityadmin fixed server role, the db_securityadmin fixed database role manages security. In this case, it manages role membership (with the exception of db_owner) as well as permissions on securables. As a result, it's another role you want to keep a close eye on. Generally speaking, I've not seen a lot of folks use this role. Typically the DBAs manage security within the database and they're already coming in as dbo. There may be some rare instances where it would be used, but I would flag those as exceptions. Therefore, if you see any members of this role within a database, it's worth checking out.

  • The db_securityadmin role can manage role membership and permissions on securables.
  • Again, since DBAs usually manage security and are usually coming in as dbo, this role is little used.
  • Normal users should not be a member of this role.
  • Applications should tend not to need this role.
  • Since it's little used, you should audit its membership for exceptions.

db_accessadmin

The db_accessadmin role also manages security, but handles access to the database, as the name implies. The db_accessadmin role grants, denies, or revokes permission to enter the database for logins. Combined with db_securityadmin, and you can completely manage security into and throughout the database. Like db_securityadmin, though, access into the database is usually handled by DBAs. If they aren't members of the sysadmin fixed server role, they are members of the securityadmin fixed server role. As a result, this role should also be rarely used.

  • The db_accessadmin role can allow access into or block access to the database for logins.
  • Again, since DBAs usually manage security and have an appropriate server-level role, this role is little used.
  • Normal users should not be a member of this role.
  • Applications should tend not to need this role.
  • This is another role you should audit for membership exceptions.

db_backupoperator

The db_backupoperator allows a member of the role to take backups of the database. However, it's only going to allow native backups, as in the standard backups through SQL Server itself. If you're using a third party product, chances are it is usually the methods which allow for high speed backups. Unfortunately, these methods require the login executing them to be a member of the sysadmin fixed server role. As a result, this role tends to be of limited usefulness. Add to it that you're backing up to a local drive, and it's rare to see a non-DBA having this level of access, even in a development system. Because of all these things, this is another role that is typically not used much.

  • The db_backupoperator role allows a user to take backups of the database.
  • Most 3rd party backup utilities utilize methods that require sysadmin rights, which this doesn't give.
  • Another role that is little used because this functionality is usually handled by DBAs or a service account.
  • Normal users should not be a member of this role.
  • Applications should tend not to need this role, though I have seen exceptions.

db_ddladmin

The db_ddladmin is another powerful role because it allows a user to create, drop, or modify any objects within a database, regardless of who owns it. So a user could alter a stored procedure owned by dbo, for instance. This role is sometimes given to developers on non-production systems as they built custom applications. However, there is typically no reason anyone should be a member of this role on a production database. One thing the db_ddladmin does not do is allow the user to alter permissions on the objects. So a member of this role can create or modify the object, such as a stored procedure, but not alter the permissions on it unless he or she is the owner. So, for instance, a member of this role could create a stored procedure in a schema owned by dbo, but couldn't grant the ability to execute it.

  • The db_ddladmin role can create, drop, and alter objects within the database, regardless of who the owner is.
  • The db_ddladmin role cannot alter security.
  • It is not unusual to grant this role to developers in a non-production environment.
  • Normal users should not be a member of this role.
  • Applications should not need this role.
  • No one should normally be a member of this role on a production database.

db_datareader

The db_datareader role allows a user to be able to issue a SELECT statement against all tables and views in the database. DENY for a user (or a role the user is a member of) will still block the SELECT, however. But if there are no permissions set, whatsoever, the user will have the ability to SELECT against the table or view. The catch with this role is that the permission is implicit. That means if you query sys.database_permissions, you will not see any permission granted, either to the db_datareader role or directly to the user. Therefore, if you need to audit for everyone who has SELECT access to particular tables in a database, you'll have to query the membership of this group via the use of sp_helprolemember:

EXEC sp_helprolemember 'db_datareader';

It is not unusual to see the db_datareader role used in databases. It's an easy way to grant SELECT permissions to everything without having to worry about it. However, due to the fact that it uses implicit permissions, I prefer to create a user-defined database role and explicitly grant permissions. With that said, here are things to remember:

  • The db_datareader role gives implicit access to SELECT against all tables and views in a database.
  • In SQL Server 2005 and up, an explicit DENY will block access to objects.
  • It is not unusual to see this role used in production for developers.
  • It is not unusual to see this role used in production for normal users.
  • Applications will occasionally need this role.
  • Creating a user-defined database role and explicitly defining permissions is still preferred over the use of this role.

db_datawriter

The db_datawriter role is like the db_datareader role in that it gives implicit access to tables and views within a database. It also can be blocked by an explicit DENY for the user or for a role the user is a member of. Unlike db_datareader, however, db_datawriter gives INSERT, UPDATE, and DELETE permissions . Again, since the permission is implicit, you will not see these rights show up in sys.database_permissions. And like with db_datareader, you'll have to check the membership of this role to determine actual permissions in the event of an audit.

  • The db_datawriter role gives implicit access to INSERT, UPDATE, and DELETE against all tables and views in a database.
  • In SQL Server 2005 and up, an explicit DENY will block access to objects.
  • Typically developer are not members of this role in production unless all users are.
  • While less common than with db_datareader, it is not all that unusual to see this role used in production for normal users.
  • Applications will occasionally need this role.
  • Creating a user-defined database role and explicitly defining permissions is still preferred over the use of this role.

db_denydatareader

Unlike the previous two roles, db_denydatareader denies access. In this case, the db_denydatareader is the same as having a DENY for SELECT on all tables and views in the database. Because DENY trumps everything else, this is not a role I've seen used frequently. If there are no permissions for a given user on an object, such as the user has no SELECT permissions on a table, then SQL Server blocks access. Therefore, if a user doesn't have SELECT permission on TableA, then the user cannot successfully issue a SELECT query against TableA. An explicit DENY is not needed. And since this affects all tables and views, that adds to the reason this database role is typically not used. And like db_datareader and db_datawriter, the DENY is implicit, meaning you'll have to query for membership in this role to determine who is affected.

  • The db_denydatareader role is denied access to SELECT against any table or view in the database.
  • Typically this role is not used.
  • The DENY is implicit.
  • Creating a user-defined database role and explicitly defining permissions is still preferred over the use of this role.

db_denydatawriter

Wrapping up our list of roles is db_denydatawriter. The db_denydatawriter has an implicit DENY on INSERT, UPDATE, and DELETE for all tables and views in the database. Again, this is not a role that sees much use, for the same reasons as db_denydatareader.

  • The db_denydatawriter role is denied access to INSERT, UPDATE, or DELETE against all tables and views in the database.
  • Typically this role is not used.
  • The DENY is implicit.
  • Creating a user-defined database role and explicitly defining permissions is still preferred over the use of this role.
Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author K. Brian Kelley K. Brian Kelley is a SQL Server author and columnist focusing primarily on SQL Server security.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2009-12-14

Comments For This Article




Wednesday, August 12, 2020 - 8:21:08 AM - ephram Back To Top (86290)
Let's say I have one db user u1.
I can make u1 as a member of db_owner.
But How can I make u1 as dbo user
Does it mean make the authorization
Of the db u1.like
Create database sample authorization u1
Thanks

Tuesday, May 10, 2016 - 5:11:28 PM - Errol Anderson Back To Top (41451)

On another forum (ServerFault) I have received the suggestion that the code should formally add the dbo owner whenever new tables are created in a database upgrade.  This sounds a good idea - is it likely to cause problems?

Monday, May 9, 2016 - 5:21:53 PM - Errol Anderson Back To Top (41442)

Hi Brian

Thanks for your reply.  Here you are out of my comfort zone as I am not a SQL Server expert, simply trying to provide a data management app to the geothermal industry, and finding that permissions continue to be a vexed issue, not just for me but for the organisations that use the app.  All I know is that in most (if not all) instances, if the dbo upgrades the database, the software users have no problems seeing all the new and existing tables.  This is using Windows authenitcation.  I do not know how the IT department grant user permisisons - I am not privy to this information.

What I am looking for is a way to grant a senior geothermalist sufficient rights over the app databases so that he/she can upgrade the databases without causing problems for other users, as many IT departments do not consider this to be in their job description.  To date, the only way to reliably do this seems to create a db_owner role and assign all users to this role, and use the internal app permissions to control who does the upgrades.  This might work if one member of the db_owner role can assign other members, otherwise they will have to apply to the IT department which can be time-consuming.  If there is a more elegant way to do this, then please let me know, and I can recommend it to current and future users of the app.

Thanks and regards

Errol

Monday, May 9, 2016 - 10:34:41 AM - K. Brian Kelley Back To Top (41435)

 

Hi Errol,

  I still think you've got some permission granting permission to dbo for all users in your application's database. Here's why I say this:

 

  • By default, no one starts with permissions on a new object. This is regardless of who creates the object. If dbo creates a table, there are no explicit permissions on the table.
  • By default, if you don't permission to an object, as a regular user (not in one of the built-in roles like db_owner, db_datareader, or db_datawriter), you can't see an object. Therefore, if dbo creates a new table, unless there's a permission already in place at a higher level, you can't see the table. 

 

The first default rule has always been true in Microsoft SQL Server. The second default role has been true since SQL Server 2005, when they closed the information disclosure hole where you could learn about objects you didn't have access to which was present in earlier versions.

If you query sys.database_permissions in that database, I wouldn't be surprised if you permissions granted against the dbo schema to public or to whatever roles that have been built for the application. 

You can confirm the behavior I've cited with a clean install of SQL Server by creating a brand new database, creating a login with no permissions other than the ability to connect to the database, then creating an object as dbo and seeing if that login can see the table. I say by creating a clean install of SQL Server because it's entirely possible someone has modified the model database on the SQL Servers you're using to grant the permissions I've mentioned. That's not standard for SQL Server. 

 

Sunday, May 8, 2016 - 12:55:20 AM - Errol Anderson Back To Top (41431)

Hi Brian.  Thanks for your reply and your analogy of databases like apartments in an apartment complex, as this highlights the point I am trying to make quite nicely.  The dbo can be likened to the building caretaker, who can assign apartments, allow or deny user access and even change the furniture with no problems.  A db_owner unfortunately does not have quite the same powers over the apartments they "own".  Sure, they can grant or deny access, etc, but when they try to introduce some new furniture - for example. a new "table" - the other users of the apartment cannot see it - so they trip over it and break a leg or worse.  So it is a SQL Server problem - the db_owner role is not truly equivalent to the dbo, because any new tables he/she creates are private tables and are not visible to any other users of the database.

My argument is that a senior member of the scinetist/engineer group (lets call them geothermalists) is the correct person to effect change control, as they know about the databases in use, they are familiar with the program that carries out the database change.  From experinece, leaving this up to the IT department does not work.  And yet, SQL Server does not provide the tools to allow the senior geothermalist to perform the upgrade - namely to make new tables public tables available to others.

In response to your further comments, the version is kept in each databae, and the app detects this.  The app does not check to see if the user has the appropriate permissions (if you know how to check from Delphi if a user is a dboi, please let me know) but we attempt to get around this by intermal permissions - only an Administrator can upgrade but sometimes with the passage of time, someone is granted Admin rights without corresponding SQL Server dbo rights).

Generally the IT department rolls out a new version remotely to all geothermalists.  However, if a computer misses the upgrade, then when that user starts the app, they are warned that the database is upgraded but the app isn't.  They can open with caution (this is generally less risky than opening an old database with a new program as a new table or new field will simply be ignored in the former case, but cause a crah in the latter) but are advised to contact IT for an app apgrade as soon as possible.

And regarding your last point - all databases used by the app should be upgraded when the app is upgraded, regardless of whether the partuclar geothermalist uses it.  This is not an issue - after all, this is exactly what you would expect the IT personnel to do if they were responsible for the database upgrades.  It sounds as though you don't trust the geothermalists to look after their own data (they do a perfectly reasonable job when it is in spreadsheets and files so how hard can it be to look after it in a database.  In my experience, the IT personnel will generally do a worse job of upgrading the databases than a senior geothermalist, as there is often a higher personnel turnover in IT than among the geothermalists and they are unfamiliar with the app and the databases.

So what I need is a db_owner role that is the true equivalent of dbo (including creating new public tables), but applies only to specified databases.  The change control management will be easy - at present it is a mess because of the limitations ogf SQL Server.

Thanks for your patience.

Best regards

Errol

 

 

Friday, May 6, 2016 - 9:13:57 AM - K. Brian Kelley Back To Top (41417)

 Hi Errol,

 

  SQL Server can do exactly what you're saying. Think of each database as self-contained, like each apartment in an apartment complex. I can give someone keys to access 5 of the 75 apartments or I can give them keys to access just 1 or all or any number in between. 

  What you're describing is a classic change control problem in IT and this affects any software and any platform.

 The way most applications handle this issue is to keep track of version in the database itself. The app checks and if it's not got the correct version, it reports back the problem. Some apps then check to see if the user has the appropriate permissions to do the changes, and if not, advises them of that fact. If they do, it gives them the option of upgrading. No crash.

 Consider if a scientist or engineer is a member of db_owner. And that person is the only one to have the new version of the software. If there are breaking changes with older versions, you have the issue in reverse... now someone with an older client opens the DB and crashes. Unfortunately, that's everyone but the one person with the new version. Now, to complicate things, the scientist or engineer just had the option of upgrading all the DBs and did. Most of those DBs weren't used by that particular scientist/engineer. 

That's why I say it's not really a SQL Server issue, but a change control one.

 

Friday, May 6, 2016 - 1:05:52 AM - Errol Anderson Back To Top (41412)

Hi Brian

Thanks for your response, but clearly I did not explain myself well enough.  I am not a SQL Server expert, I merely supply software that stores data in a SQL Server database.  The data is "owned" by those who use the software - in other words, they are a group of scientists and engineers collect the (geothermal) data from the field, assemble it in spreadsheets, review it for errors, enter it in the appropriate tables of the database and then use it to assit their management of the geothermal field.  Each company who uses the software have one or more databases with identical structure, corresponding to the number of geothermal fields they operate, and generally named after each geothermal field.

From time to time, the database needs to be upgraded, and this might require the creation of a new table (e.g. to hold data from a new type of measurement).  A new version of the software is released which contains the information about the new structure in the data model.  Someone installs the new program, and then needs to run the program and select each database in turn, and will be prompted to upgrade the database.  If that someone is dbo, there are no problems - alll new tables are owned by dbo and every user can access this noew table. 

However, the dbo (usually a member of the IT staff) is often not aware that he/she has to run the program and select each database in turn, and even if they do run the program, they may not have an up-to-date list of current databases that need upgrading and no idea how to obtain this list.  So the program is installed, one of the scientists or engineers runs the program, selects a database that is not upgraded and either cannot upgrade it or upgrades it but the new table then belongs to that user and is not available to other users of the databae, so it crashes.

So as far as I can see, there are three possible solutions, all of which are flawed:

1. Insist the dbo upgrades all databases.  However, this will require a close liaison between the scientists and engineers and the dbo, to ensure all new IT staff with dbo credentials know what to do as they are appointed.  However, this often fails, as the dbo staff consider this to be outside their job description.

2. A dbo_owner role is created for each of the software databases and every scientist / engineer who uses the software is assigned a memeber of this role.  Then any one of the group can upgrade the database without problems.  The new table will be owned by the db_owner role, but all members of this role can access these tables.  A "guest" login could be created for users who want occasional read-only access (the software can internally enforce read-only permissions).  The problem with this solution is that many IT personnel are completely opposed to granting db_owner rights, even though this appears to be the only reasonable solution.

3. Grant one or more of the senior scientists or engineers dbo rights, and allow only these personnel to upgrade databases (easily enforceable by the internal permissions).  Unfortunately this has even more opposition from IT personnel.

I have been trying to find a reasonable answer to this problem for some time now, but it appears that it is far outside the experience of most database experts.  I am trying to do something different with databases - make them act more like a giant interconnected multi-level spreadsheet rather than a mysterious back-office beast that is totally in the hands of IT.  I feel that SQL Server is flawed in that it cannot apply a true dbo role to just a feww specified databases, so users have true control over their corner of the enterprise.  I must point out that when this data is in spreadsheets and files, the creators and users of the data have complete control, but once it is in a database (which ideally is more robust and secure than spreadsheets) then control is taken away and put in the hands of IT.

I look forward to your comments, and I would like to hear from the wider database community.

Best regards

Errol

 

 

 

 

Monday, May 2, 2016 - 9:02:28 AM - K. Brian Kelley Back To Top (41384)

 

Hi Errol,

 

  I think likely you've got permissions assigned against the dbo schema. Because by default, when you create an object in a SQL Server database, no one initially has permissions directly against it except the owner. However, if one were to have permissions set at the schema level, then the permissions carry down to the new table. Obviously, db_datareader and db_datawriter roles have implicit permissions.

 

Friday, April 29, 2016 - 9:35:09 PM - Errol Anderson Back To Top (41369)

 I liked your comments about the db_owner role but I must point out that db_owner is distinctly different when it comes to creating new tables in the database.  If a dbo, the table, owned by dbo is accessible by other users of ther database.  If a db_owner does this, the table is owned by db_owner and is NOT available to other users of the database.

This is an issue for the reasons described below:

We supply specialised data management software to the geothermal industry. From time to time, the database structure needs to be changed, and we issue a new version of the software that automatically updates the database structure as each database is opened. In most companies, IT personnel install the new version, but are generally unaware of the need to run the program, select and open each database and run the update procedure (this seems to be outside their job description). Consequently, when the users start the program, the database is not updated, and they do not have rights to do so.

The obvious "solution" is to make one user a member of a db_owner role for the specified databases, and give them rights to update the databases (set inside our program). However, this does not work, as any new tables are "owned" by that user and are not visible to the other users. So, the db_owner role is NOT equivalent to dbo for creating new tables.

Some companies have assigned all users to the db_owner role for the specified databases. This works fine, but many IT personnel take exception to allowing all users such high-level permissions.

Other commentators have suggested that the installer, setup.exe, should handle the database upgrades.  However, each user of the software will have different databases (typically a separate database is assigned to each geothermal field that the user is operating, which could range from 1 to many).

So, my question is - is it possible to grant true dbo-type permissions to specified databases so that new tables are readily available to all users of the program.   As far as I can see, the inability of SQL Server to provide a true dbo role but just to limited databases is a severe shortcoming of the program, especially for database applications where the data is "owned" by the users. 

I look forward to a solution to this problem. 

Regards

Errol

 

 

0 down vote favorite
  

 

 

Wednesday, October 29, 2014 - 3:02:12 PM - TechnoJoe Back To Top (35117)

This highly detailed page defines each of the specific permissions granted or denied under each role.

Permissions of Fixed Database Roles

http://technet.microsoft.com/en-us/library/ms189612(v=sql.105).aspx

Sunday, January 26, 2014 - 5:37:24 PM - Errol Back To Top (29227)

I manage and internationally market a "universal" geothermal data management program, written in Delphi, that is a front end to a SQL Server database.  The data in the database is derived from many diverse measurements generated and used by the program users over time periods of 30 years or more - i.e. they "own" the data, and the database is primarily a way to efficiently store and manage the data.

Like all databases, the database structure needs to be modified from time to time, including new tables, and this modification is delivered by the release of a new version of the program.  The program prompts for a database upgrade, which has to be carried out by the dbo user so that all new tables can be accessed by the other program users.  Unfortunately, the program may be used in remote sites and the IT personnel may not be readily available, so that the new version does not get installed.  What has frequently happened in such locations is that one program user will upgrade the databases without appropriate SQL Server permissions, and then the other users cannot access the new tables.

One program customer has taken another approach.  They have created a db_owner role for all the databases used by the program and then make all program users members of the db_owner role.  The program has inbuilt permission levels that can restrict the ability to upgrade databases, so normally only one or two users have this permission.  However, with everyone a member of the db_owner role, it doesn't matter who upgrades the database, all tables will be accessible to all program users.

The advantage of this approach include the following:

1. Access permissions can be granted by the group who uses the program, and who has ultimate responsibility for the database.

2. Knowledge and understanding of the program is passed on within the program users group when staff changes, rather than having the IT department the repository of information on "how it works".

3. Direct data-mining and back-door data modification is possible to selected user experts.  While the program has extensive datasearch and editing tools, sometimes these are not enough.

4. The program users retain "ownership" of their data.

I would appreciate your comments.  I believe that in some circumstances it is important that database users are db_owners, and blanket comments that db_owner should be restricted solely to the IT department does not recognize the importance of data ownership and data accessibility.

 

 

 

Friday, January 3, 2014 - 4:16:40 AM - Akshat Ratanpal Back To Top (27946)

Hi,

What does 'AssociatedRole' 'NULL' means? What are the entitlements for Users/Groups with 'NULL' as their AssociatedRole?

Friday, September 13, 2013 - 5:09:06 PM - Geoff Back To Top (26793)

Thanks Brian!  Great post on SQL server database roles.  Since I am fairly new to SQL Server I needed a bit of a refresher on roles in general.  Found a good resource here also:

http://www.programmerinterview.com/index.php/database-sql/database-roles/

Thursday, August 9, 2012 - 9:44:04 AM - lcad Back To Top (18978)

Is there a built-in role which allows making changes to a database schema/settings and all other typical operations on a database but does NOT allow dropping the database? Thanks

Monday, June 18, 2012 - 2:51:08 AM - Sathish kumar Back To Top (18080)

Thanks brain!! , this is very lucid and easy to understand.

Monday, April 16, 2012 - 2:35:41 PM - Cita Back To Top (16950)

Is there a way to grant developers with "db_owner" role to see also the SQL Server agent jobs? Is so, how?

 

Friday, January 21, 2011 - 6:01:10 PM - mssqldude Back To Top (12668)

Brian - Thanks for the series on the built-in roles, these are very important to understand. That being said, for many of these you (correctly) make the point, "Creating a user-defined database role and explicitly defining permissions is still preferred over the use of this role". Makes me wonder if you should just put that in the opening paragraph and just say to heck with using fixed roles!?

Friday, December 18, 2009 - 12:03:51 PM - BillTalada Back To Top (4588)

Thanks for the very informative article on fixed database roles.  I'd just like to add that the db_datareader and db_datawriter are generally both required in tandem since virtually no one updates without first selecting data. Also, it would be nice to have a fixed database role for executing procedures so we don't have to individually grant hundreds of procs to each user or group.  Most applications use about 90% stored procedures and the other 10% sql dynamically built and sent to the database, hence the need for selects, updates, and executes.

 Applications generally use the dbo schema for everything which is a sub-container for security in the database.  I've found it easier to stop using fixed database roles altogether and switch to schema security which is as easy as the following:

grant select,insert,update,delete,execute on schema :: dbo to AppUser

This removes the need for having GRANTS in every stored procedure script.














get free sql tips
agree to terms