Scripting Database Roles For Deployment Upgrades Into Production
Oct 2, 2007
I have searched hi and low for hours within the product and online - I cannot find how to have SQL 2005 produce a script to automatically repeat the creation of a database role I have tedious manually created.
I have tried "script object as, create to,<>" - single-liners are produced - no details. More single line results from "<Database>, All Tasks, Generate Scripts".
Am I missing something or is this a bug?
€¦also to note after review blogs and other sources the use of the SQL2005 methods related to security and new schema objects - I feel€¦ dreadfully wrong - what is claimed feels like hype, unsupported by examples and usage cases feels like we better just make all automated processes sysadmin; I know learning curves can be strong but I am not this green or that stupid - where are the DOCS/training materials?
For the past few months I've been developing an DW and ETL with SQL 2005 / SSIS. My packages are being deployed to a SQL Server. Although in the end game we will have a Dev/Staging/Production environments, I would still like to archive production packages when we push staging to production. Essentially I would like to archive the last X packages that were deployed to production where X is a reasonable number (3 - 5). I don't necessarily need to have them accessible to run. One of the purposes is to have another safeguard should we miss anything in user testing and need to roll back a deployment.
I am utilizing VSS and we will have backups running on the production server, but I would prefer to have a archive that is a little more accessible.
I just wondering if anyone has any thoughts on how to extract/archive production packages when the push is made. I could easily develop an app that queries the MSDB and exports the packages to the file system.
I am new user of SQL Server. I have some problems with these words. I want to make my database works in my specified permissions. I will specify permissions with schemas and these schema wants an owner. I want this owner should be my user. When creating a user it needs a valid login. I am selecting my login and it occurs and error says this login has an different user. I am specifying permissions with roles. But i can't make association all of them. I hope i told my problem to you as well. If you explain these words to me and tell me how can i do my database's works with my own schemas, users and roles i'll be grateful. Thanks for advices.
After reading Books Online, I am still confused with Database Role vs Application role.
My intention is to control the end users' authority on the database, where the end users will access through Winforms client application. With proper assignment of schema and database roles to an user, I believe this will enough to control the permisison of an user.
If this is the case, why Application role exists? When and why should I use Application Role? How is it different from Fixed Database Role?
We are setting up a new Reporting Services 2005 enterprise reporting tier that will support multiple developers, applications, and end users. We will have mirrored environments including development, test, and production each with their own database cluster, and reporting server.
We have multiple report developers who share a single Visual Studio solution which is saved in SourceSafe and is setup to have separate report projects for each business unit in the orgainzation. Each report project is mapped to a specific deployment folder matching the business unit. Using the Visual Studio Configuration Manager, we can simply flip to the envirnoment we want to deploy to and the reports are published to the correct environment and folder structure.
My problem lies with the common data sources. We are using a single master Common Data Sources folder to hold all of the data sources. The trick is that each and every reporting folder seems to have to have it's own copy of the data source in visual studio. There does not seem to be an easy way to change the data sources for the reports when you publish to various environment, i.e. development, test, production etc.
Ideally, we would have a single project for the common data sources that all reporting projects and associated folders would map to, and we would have a way to associate the appropriate data source for each environment when we deploy.
I'm looling for best practices on how to setup data sources for development and deployment in an enterprise environment that uses Visual Studio to develop and publish reports. We have 3 environments, and 6 data sources per environment and about 20 reporting folder / project in Visual Studio. That's 360 changes that have to be manged when deploying reports. Is there a best practices way to do this?
There has got to be a better way? Can anyone give me some insite into how to set this up?
I am writing a new version of a task with and additional property, and wish to support upgrades between the versions. I have implemented the CanUpdate and Update methods, and update the Xml for the new property. The problem I then have it LoadFromXml gets called which promptly fails since it is still using the old version of the Xml. Have I misunderstood the point of the Update mechanism? It would seem to me that I€™d better off ignoring Update and implementing the checks defaulting my new property within LoadXml. The new version of SaveToXml would sort out the upgrade when saved again.
I have a notebook with windows vista ultimate edition. For developement i have Ms visual studio 2005 and sql server 2005(with all the required and latest SPs) on it. For the past few months i have been working on the machine without any hitches. Just 2 -3 days back some windows updates got installed on the machine and guess what!!!!!! the sql server service just stopped working. The service wont start no matter what. I have tried almost solution that i came across on the internet and it wont work out. I unistalled and reinstalled the server as but to no avail. The database engine now wont intall as the sql service wont start during the server installlation.
While browsing through the internet i didnt come across anyone with a similar problem! Am i the first one to be in such a situation give your comments and share any relevant experience.
P.s the windows upgrades installed on the system were all security upgrades so why should they effect the sql server working.
What are the differences between the database permissions, which can be granted in the database properties permissions tab (create table, create procedure etc.) and the predefined role db_ddladmin? It seems that the database properties permissions tab includes more permissions than the predefined role db_ddladmin.
Does anybody know the difference in terms of permission?
Apologies if my post does not fit into this forum. I initially tried the SQL Server Data Access forum but I now think my question is more security related.
Is it possible for a web user who has been successfully authenticated with forms authentication to be authorised to use a SQL Server 2000 role depending on a particular ASP.NET 2.0 role that they have been authorised to use? I understand that that I can assign a SQL Server 2000 role to the ASPNET or NETWORK SERVICE account but this will grant access to anonymous web users to the database role. I can ensure that I only call stored procedures which access sensitive data in web pages that are in restricted by ASP.NET roles. However, it would be nice to also restrict stored procedures via the ASP.NET 2.0 Forms Authentication roles.
If this is not possible have you got any bright ideas how I could restrict access to stored procedures who are anonymous web users.
I'm developing an ASP.NET2.0 application which accesses a SQL Server 2005 Express database. I plan to use integrated security for access to the database.
I'm confused about the relationships between Windows groups, the ASP.NET web.config file <allow roles=.../> and SQL Server roles.
I would like to create a Windows group to which I can assign multiple users and grant that group access to a Web Site using windows authentication and also grant that windows group access to the database my web application uses.
I have gotten the combination of Windows Authentication to the web site and to the database to work for a specific windows user but I am having trouble determining the combination of database security entities I must create to allow access to my database by members of the windows group.
For a Windows user:
1. Create Windows user
In SQL Express
2. CREATE LOGIN FROM WINDOWS WITH DEFAULT_DATABASE =
3. CREATE USER FOR LOGIN
4. CREATE ROLE
5. EXEC sp_addrolemember <role-name> <user-name>
For a Windows group, what would be the equivalent commands necessary to grant a windows group access to my database? Specifying the Windows Group name in sp_addrolemember does not appear to be sufficient even though the documentation states that a windows group name is a valid value for the member name argument.
I just starting working for a new client this week. They have a custom install program written in VB.NET which installs the database to SQL Server 2000. Normally, when I want to create a SQL script to create a database, I would go into Enterprise Manager, right-click on a database, and select All Tasks | Generate SQL Script. However, that's not how this client works. Much to my horror, the CREATE DATABASE, CREATE TABLE, etc. statements are both hard-coded and hand-coded within the install program itself. When I asked them why they weren't using SQL Server's scripting capabilities to automatically generate the script, they said that SQL Server scripting is buggy and that it doesn't work properly. It was hard getting specific details but they said it would leave out indexes, for example. Now, I'm no SQL Server expert, but I've never had a problem using SQL Server's scripting capabilities. The database they use isn't that big - maybe 20-30 tables. So, before I make an issue out of it, I was wondering what everyone else's experience with SQL Server 2000's scripting features? If you've used SQL Servers scripting to recreate a database, does it work? Did you have issues?
Can you write a script that compares table changes. I have a testing database with a bunch of table changes that i need to move to production and instead of handtypeing the changes in I was wondering if there was a method of creating a script.
I am not sure if this is the right place for this question or not but here it goes...
I want to add a new user to the database with read-only rights. I know public is selected by default and can not be changed. I also added the user to db_datareader and db_denydatawriter. But just as I was about to save I noticed Read Only. I have done some searching but have not found anything about this role. I assume it does just what it says but I am confused as to why it would be there when I can set the other settings I just did. Is there any benefit to using this role instead of the ones I chose? Should I add this role in addition to the two I have already picked? Any help is greatly appreciated.
Hi there,Getting ready for deployment and I would like to be able to run an installscript that will not just generate the schema, but fill in some of thetables with default data. Is there a tool out there that will scan usertables in an SQL database and generate the script required in order tocreate a database with this default data?ThanksRobin
On 11/10/01 I posted a question on how to move passwords from one sql2000 box to another. The response to this question worked perfectly.
I also need to copy over the user or database access information from one box to another. I tried to use dts but it only copied over the users and the database roles but did not copy over the Database user properties which allows the user access and places the user into security groups.
After "copying" a SQL Srv DB from one server to the next, we have found that the permissions within the developer-created database roles did not transfer. How can we insert these permissions without doing it manually? Or can we create some type of DTS package that will bring the roles' permissions for us? Or how do you move a SQL database from one server to the next and get everything within it to come along??
I have been doing maintenance on my sql server 7 databases, I have removed some users from the server login area for example user "EMS". I noticed that "EMS" is still in the 'public database role' for my database. How do I remove old unwanted members out the public database role? Are they perminantly stored there? Is there a way to clean them up?
Does anyone know why users would notbe listed while adding them to roles?
I'm looking for advice/caveats about how to convert/export information on AS database / cube roles. The reason why is because we have to move the server into different windows domain and all the roles reference windows accounts in the old domain. Using the MS SQL 2000 version.
Hi I'm trying to script an Admin user into my SQL set up scripts. I'm ok with creating the database, adding tables and populating the tables I've created and thought that I could just EXEC aspnet_Membership_CreateUser '/', 'Admin', 'SecretPassword'...to achieve my goal, but it seems that the framework is responsible for encrypting the password before it gets to the Procedure and clear text just isn't acceptable.Any ideas on how to go about adding the user through SQL?
I am able to run the package successfuly in test database. but not in production database. It throughs up error saying
Description: Unable to load the package as XML because of package does not have a valid XML format. A specific XML parser error will be posted. Description: Failed to open package file "D:\TAHOE\APPS\SSISPackages\Integration Services Packages\ArchiveMain.dtsx" due to error 0x80070015 "The device is not ready.". This happens when loading a package and the file cannot be opened or loaded c orrectly into the XML document. This can be the result of either providing an incorrect file name was specified when calling LoadPackage or the XML file was specified and has an incorrect format. End Error Could not load package "D:\TAHOE\APPS\SSISPackages\Integration Services Packages\ArchiveMain.dtsx" because of error 0xC0011002. Description: Failed to open package file "D:\TAHOE\APPS\SSISPackages\Integration Services Packages\ArchiveMain.dtsx" due to error 0x80070015 "The device is not ready.". This happens when loading a package and the file cannot be opened or loaded corr ectly into the XML document. This can be the result of either providing an incorrect file name was specified when calling LoadPackage or the XML file was specified and has an incorrect format.
Hi! Can anyone say which ms sql server predefined roles are similar to the following oracle predefined roles: dba, connect, resource. I already know that sysadmin in MS SQL Server is the same as DBA in Oracle but what about the rest? Thanks a lot.
I am in the process of locking down the SQL Server in an environment that is considered to be in production (pilot stages) and there is no staging or test environment that mirrors it. I need assistance in determining the server and database roles to assign to existing logins, most of which currently have sa and dbowner rights. Because it is not a development environment, I need to be sure that downgrading the server and/or database level permissions will not break any functionality.
I'm starting with the logins that have the SA fixed server role. These logins need to be able to install applications that require the use of a backend database, which will be stored on SQL Server. In addition, through the installation process a new login/password for the newly created database(s) is normally created. For the existing logins with the SA fixed server role, will downgrading to the securityadmin and dbcreator roles be sufficient to facilitate those needs, or are those too much/ too little? And should any user account ever be granted the SA role? If so, what questions could I ask to determine this need?
Since these install process for these applications usually prompt to install using SA or local system account to authenticate to SQL to create the new database(s), that account should have securityadmin and dbcreator roles to create the database and its tables, as well as add a new login to that database.
Please address this question, keeping in mind that the logins will only be performing the described actions, installing apps using SQL Server as the backend database and adding a login to that database (which may or may not be done during the installation process).
I have an application that segregates data into two differentdatabases. Database A has stored procs that perform joins betweentables in database A and database B. I am thinking that I have reachedthe limits of Application Roles, but correct me if I am wrong.My application creates a connection to database A as 'testuser' withread only access, then executes sp_setapprole to gain read writepermissions. Even then the only way 'testuser' can get data out of thedatabases is via stored procs or views, no access to tables directly.Anyone know of a solution? Here is the error I get:Server: Msg 916, Level 14, State 1, Procedure pr_GetLocationInfo, Line38Server user 'testuser' is not a valid user in database 'DatabaseB'The system user is in fact in database A and B.thanksJason Schaitel
I am not sure why this is not working it clearly states in the MSDN that it should "
'role' Is the name of the SQL Server role being checked. role is sysname and can include the database fixed roles or user-defined roles but not server roles.
"
I have seen many questions revolving around this issue on this site and on the net about this but know one can answer it
I have created a new user defined database role called testrole with any owner
then created a new sql login and user (Sql Authentication)
add the user to the database role testrole
check IS_MEMBER and it returns 0
try this with a fixed database role and I get the desired result of 1
this is simple and should not be such a problem for every one
