April 6, 2011 at 7:12 pm
I manage a sql2008r2 database server for an ecommerce site. I recently implamented Roles and granted SELECT,EXECUTE,INSERT to the role and added the users. Everything worked perfect. I got a request to run a query that was modifiing a current SPROC. Once i ran it the site came down due to role not having SELECT,EXECUTE AND INSERT. Please tell me that the permissions to the role are not removed or altered once a procedure is modified. I cant have the site come down every time schema is modified. Thanks
April 6, 2011 at 11:08 pm
How are you modifying the SPROC?
If you do like a lot of people and DROP the procedure and then CREATE it again, yes the permissions are lost. You can use ALTER PROCEDURE to update the procedure and keep the current permissions. (But then the script won't work for a new procedure.)
You have two options:
* Script the permissions as part of your DROP/CREATE script.
* Instead of DROPping the procedure, create a stub and use ALTER:
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = '<your_sproc_name_here>' AND ROUTINE_SCHEMA = '<your_schema>')
EXEC ('CREATE PROCEDURE <your_schema>.<your_sproc_name_here> AS SELECT ''stub procedure to allow ALTER.'';')
GO
ALTER PROCEDURE <your_schema>.<your_sproc_name_here> AS
<your code here>
GO
April 6, 2011 at 11:31 pm
I've seen permissions disappear from a simple alter of the proc as well (not drop/create). I think the safest bet is to make sure you apply permissions to the proc after any changes.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 6, 2011 at 11:49 pm
CirquedeSQLeil (4/6/2011)
I've seen permissions disappear from a simple alter of the proc as well (not drop/create).
Wow, we have never run into that, and we use ALTER a lot on procedures with permissions.
I would be interested in seeing a way to replicate it. (What version of SQL Server have you seen it in?)
April 6, 2011 at 11:51 pm
2000 and 2005
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 7, 2011 at 12:32 am
Ok so now i don't get the point of the role. Of course the Developers always want grant dbo to the sql account. Just doesn't seem right. Is there another route i can look into? Obviously I cant keep recreating the role and the permissions. It's 6 clusters (2node each) and it would just be to much. I also cant break the current sessions with ever upload. Some times 1 a day or 6 a day. I would be running queries and checking permissions all day. Any feed back would;d be appreciated. The most i gave a role is SELECT, UPDATE,INSERT,DELETE,
April 7, 2011 at 12:34 am
2008r2
April 7, 2011 at 1:45 am
It should not occur with an ALTER. If you see that, please report it via connect.microsoft.com as a bug.
With that said, starting with 2005, you can apply permissions at the schema level. So if I have objects in the dbo schema, if I apply SELECT to the dbo schema, then all of the objects contained in the dbo schema for which SELECT applies (tables, views, table-valued functions) receive the SELECT permission implicitly.
This may be a better option because it would mean that even if objects are changed within the schema, the permissions remain.
K. Brian Kelley
@kbriankelley
April 7, 2011 at 10:14 am
ljf3rd-1038030 (4/7/2011)
Obviously I cant keep recreating the role and the permissions.
If the role is being lost when you run your update scripts you need to look and see what you are running. Even dropping and recreating a stored procedure shouldn't drop/remove any roles. Any chance you can post one of the scripts that you are seeing lost roles and permissions with?
As K. Brian mentioned you can assign permissions at the schema level, so as long as they need the same permissions on every object contained in that schema, including any future objects that will be created. (If you are giving SELECT, UPDATE, INSERT, and DELETE to the dbo schema and there aren't other schemas you might as well use the db_datareader and db_datawriter roles to cover that. Then you would just have to worry about EXECUTE permissions.)
April 7, 2011 at 10:17 am
UMG Developer (4/7/2011)
ljf3rd-1038030 (4/7/2011)
Obviously I cant keep recreating the role and the permissions.If the role is being lost when you run your update scripts you need to look and see what you are running. Even dropping and recreating an stored procedure shouldn't drop/remove any roles. Any chance you can post one of the scripts that you are seeing lost roles and permissions with?
As K. Brian mentioned you can assign permissions at the schema level, so as long as they need the same permissions on every object contained in that schema, including any future objects that will be created. (If you are giving SELECT, UPDATE, INSERT, and DELETE to the dbo schema and there aren't other schemas you might as well use the db_datareader and db_datawriter roles to cover that. Then you would just have to worry about EXECUTE permissions.)
Agreed. There might be something else being run when you alter these procs.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 7, 2011 at 10:39 am
could it be something like a missing GO statement, so the DROP/CREATE rolename is part of some stored proc that gets executed?
CREATE PROC MyProc
AS
SELECT 1 from sys.objects
--missing GO
If Exists(somerole)
drop role somerole
GO --end of proc due to GO statement
create role somerole
Lowell
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply