Unanswered: [SQL2008] User Defined DB Roles and Stored Procedure Permissions
In SQL Server 2000, I always create database roles for the application (e.g. MyApp_read_only, MyAdd_admin, MyApp_supervisor). These roles have members which are Active Directory groups. Stored Procedure permissions are granted to the database roles. This way, when restoring a copy of a production DB to our test server, I just have to map the Active Directory groups to the database roles, and not redo the permissions to all the SPs (since the prod and test servers have different master databases).
Now in SQL Server 2008, I am attempting the same thing. I use Management Studio to grant permissions to the database role. Then I script it out. When running the database setup script, an error message shows:
Cannot grant, deny or revoke permissions to or from special roles. (Microsoft SQL Server, Error: 4617)
Basically, Management Studio lets me point-and-click to add Execute to the SP, but the script that it generates for me is actually invalid (!?!?!):
GRANT EXECUTE ON
[lists].[proc_read_setting] TO [sm_datareader] AS [db_owner]
The AS [db_owner] part of the T-SQL is foreign to me. And the only thing different in SQL 2008 than what I've done in SQL 2000 is that the stored procedures are owned by a user defined scheme -
[lists] in this case.
Can anyone tell me what is going on? What am I doing wrong in terms of security?
Retired Moderator at Xtreme VB Talk, for all of your Visual Basic needs.