Deploying SQL Database

Hi All,

Can anyone tell me,is it possible to create an exe or msi for sql stored procedures,tables and triggers?I want to deploy the database objects(stored proc,tables,views and functions) as an exe file..just like publishing and deploying the asp.net application.Is it possible for sql server database objects.Pls,let me know.

Thank U[:)]Usually database with all objects gets backed up and then restored on another Server. It is the best and safest way to do it.

You also can extract scripts of all objects and then run them on another Server using exe or any means which can connect to that Server.

Good Luck.

Deploying CLR Stored Procedures to a client, is there a better way

I'm developing an application locally, and after I complete major revisitions/bugfixes, I transfer the code/database changes to the client. If I change the code for a CLR stored procedure locally, I can just deploy the project to the local database, which handles everything for me.

Transferring to the client's database is more difficult, however. In order to update the CLR procedures, I need to drop my regular stored procedures, drop my functions, drop the CLR procs, and then re-add the CLR procs, functions and stored procedures to avoid errors based on dependencies. The stored procs/functions don't change, and it seems like there should be a way to just insert the new CLR stuff without dealing with the regular stored procedures or userdefined functions.

Basically, I'm asking whether there is a better way to do this, or if I'm stuck doing this excessive routine every time I need to update.

Thanks in advance

it sounds like your in the "debug" phase right now, so you could use multiple assemblies to seperate those SQLCLR routines that you know are still giving you problems and those that are not. Once fully tested you could then re-merge them back into one assembly.

You could also look at using the scripting wizard to script the assembly and all of it's dependencies from your local/dev box.

Derek

|||

Have you tried using ALTER ASSEMBLY from T-SQL to deploy changes to the assembly.

Detailed documentation on ALTER ASSEMBLY is available at http://msdn2.microsoft.com/en-us/library/ms186711(d=ide).aspx

Thanks,

-Vineet

deny permission help

Hello,
I am using sql server 7.0, I want to deny permission to certain extended procedures in the master database to all the dbowners of other databases. Please tell me the syntax of the command with example.
koolnyzeMake sure the dbo accounts don't have explicit access to your master database (the dbo in master is not necessarily the same as the dbo in the other databases). Then deny permission to the guest user role in master for whatever procedures you want to disallow.

Since you are making a change in your master database, be sure to back it up!

blindman|||Hi,

I want to deny the permission to dbowners for getting the directory list when they click on the backup destination for taking backups. I have denied execute pemission to guest and public for xp_availablemedia but still the dbowners are able to get the directory list. Can you please suggest how to achieve this?

Koolnyze|||Microsoft's support webset (http://support.microsoft.com/default.aspx?scid=kb;en-us;323249) had this to say:

"Only members of the SQL Server system administrators role (sysadmin) can view the directory structure and select local backup files. "

Try going into the SQL Server Security/Logins folder in Enterprise Manager. Right click on the login, select Properties, and go to the Server Roles tab to make sure that the login is not a member of the system administrators server role.

I didn't try this, so let me know if it works!

blindman|||"Try going into the SQL Server Security/Logins folder in Enterprise Manager. Right click on the login, select Properties, and go to the Server Roles tab to make sure that the login is not a member of the system administrators server role."

I checked it and the login does not have any role selected. The login has been made dbowner of one database only and cannot access any other database. Still the login is able to browse the directory tree. Any other seggestions.

koolnyze|||Jeez, I'm running out of ideas.

What about looking at the server roles themselves (System Administrators and maybe Disk Administrators) to see who IS a member? Perhaps your users have administrative access through their network login account?

Sorry I can't be of more help, but this is getting into an area where I have less expertise. A network administrator might be able to help you out.

blindman|||I have thoroughly checked but everything seems fine. It seems that something else also needs to be done before the permission to deny the view of directory tree will be effected.

Thanks for your help

koolnyze|||Hi,

Only denying permission to xp_availablemedia doesn't work. When I denied permission to xp_fixeddrives also, it started working.

koolnyze