Losing SQL-object rights after drop/create SQL-object

[MarnickTelenet]

We have a web-application that connects through a SQL-user to a Microsoft SQL2005-database with certain rights on certain SQL-objects (for example: only that User Defined Function, only that Stored-procedure).

Sometimes we have to update some of those SQL-objects: new or extra code in a UDF or a SP... Our tool that creates the script (ApexSQL) does this update with first a DROP object and then a CREATE object. This is interesting because in that way we can directly see in the cliënt database the creation date of the object, which you can't see with an ALTER object (there is no update-date visible in the SQL2005 server management studio).

After this DROP/CREATE is done on the databases of our cliënts, the rights for the web-application SQL-user are gone.

What is the best solution for this?

Is it possible to make a stored-procedure named "RefreshSqlUserRights" that we run after every database-update? That stored-procedure re-assings the right rights on the right SQL-objects for that SQL-user?

Anyone experience with this problem?

 

[ptheriault]

If you drop an object and recreate that object in SQL server it the same as creating a new table. It's completely normal for user right to also be dropped with that object. If you can't create the user permissions as part of you table script then you should have a script (stored proc or not) that you run after you make changes.

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[SQLDenis]

If you don't want to loose the permissions then do ALTER instead of DROP/CREATE. If you do DROP/CREATE the you have to give permissions again

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
Google Interview Questions

 

[mrdenny]

As already pointed out above (but what the hell, I like to be the chior every once and a while) do an ALTER not a DROP and CREATE.

You can view the update date via the sys.procedures DMV. Folks need to remember that the GUI isn't the only way to view the info. Don't be afraid to type some code. You'll get much more (and sometimes better) information about the system with a little code punching.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com