How Would You Modify These Permissions In SQL 2005 For Minimal Exposure
Jan 20, 2008
I have finally gotten a setup to work, but I suspect there are improvements I should make to the permissions. Here's the setup:
I am accessing two databases, (aspnetdb and my own database called custom) on a local SQL 2005 server.
In SQL Server Management Studio (SMS) I created logins at the level of the SQL instance for two users ( "NT AUTHORITYNETWORK SERVICE" and "viewer"). Then I select each user, and set the properties to access the Server Roles section. There I found each was given the public server role. Now here's where I set the permission to sysadmin. This setting allows my application to work, but I'm sure there are less permissive approaches I should take. However, I just can't seem to find a simple and direct explanation of the procedure to set more appropriate permissions. The "NT AUTHORITYNETWORK SERVICE" accesses aspnetdb in order to create users and membership settings. The "viewer" login accesses the database called custom, and it reads and alters this database. The application (which is name viewer) performs these operations using a connection string like this:
Data Source=STORE;Initial Catalog=custom;Persist Security Info=True; User ID=viewer; password=xxxxx;
What is the recommended way to set the minimal permissions for these logins on these databases?
We use Netbackup for our SQL servers to backup and restore databases. I would like the service account used by Netbackup to have as limited permissions as possible. The account should be able to backup and restore a db without being able to read any of the content. Right now the account jobs fail if the service account is not in the sysadmin role.
I removed the account from sysadmin and limited it to dbcreator and public but the job fail.
How to setup an account so that people who know the service account password can't log in with that account and read db information?
We have a SQL 2012 SSRS instance. We need to modify the permissions on one of the reporting folders. I went to the Security setting of the folder, clicked "New role assignment", then entered a group name into the box and selected desired permission ("Browser" in this case).
However this always fails with the same error: "The user or group name 'BUILTINBUILTIN' is not recognized. (rsUnknownUserName)"
I always get the same error no matter if I enter user or group, domain or local. And it always says BUILTINBUILTIN. I've tried changing the SSRS service account to a domain account (it was a local one), but that didn't work.
Last night at home on my 64 bit Vista machine, I encountered the same error 29506 that said that the management studio express could not be installed. I looked up the error message and below is what I received. I also installed the 64 bit .net framework which installed just fine before trying to install the SSSME.
I followed the instructions below but this did not seem to make a difference. However, I did not reboot after applying new permissions. I have installed this software a few times now on 32 bit machines for both XP and RC1 Vista, and have not had a problem. Last night, I did use the 64 bit version. Also, there is no data file yet because after the install it rolls back, so I gave myself Full permissions on the SQL server directory which should include all sub directories, right? Thanks, Teri Error 29506. SQL Server Setup failed to modify security permissions on file Drive:Program FilesMicrosoft SQL ServerMSSQL.xMSSQLData for user SYSTEM. To proceed, verify that the account and domain running SQL Server Setup exist, that the account running SQL Server Setup has administrator privileges, and that exists on the destination drive.
Note A SQL Server service pack refers to SQL Server 2005 Service Pack 1 (SP1) and later service packs. CAUSE This problem occurs because one or more data files exist that do not have the required permissions. By default, the Full Control permission of the Administrators group is granted to the data file when you create a database. If the permission of this group is removed from the data file, the SQL Server 2005 service pack setup will fail. RESOLUTION To resolve this problem, grant the Full Control permission to the Administrators group on all data files and on the Data folder. To grant the Full Control permission to the data files, follow these steps:
1. Locate the folder that contains the data files. By default, these files are located in the following folder: C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLData
2. Right-click the data file that has no required permissions for the Administrators group, and then click Properties.
3. If the Administrators group is not in the Group or user names list, click Add, type Administrators, and then click OK.
4. Click Administrators in the Group or user names list, and then click to select the Allow check box for the Full Control item in the Permissions for Administrators list.Note If the files in the Data folder have an orphan owner, you have to take ownership of the files and then grant the Full Control permission to the files. We recommend that you do not change the default permissions for the data files.
MSI (s) (D8!A0) [21:07:09:062]: Product: Microsoft SQL Server 2005 -- Error 29506. SQL Server Setup failed to modify security permissions on file C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLData for user Administrator. To proceed, verify that the account and domain running SQL Server Setup exist, that the account running SQL Server Setup has administrator privileges, and that exists on the destination drive.
Tried running install with a domain account and local account with same results.
Based on the error message, I checked permission on the drive and still received the same error.
Followed resolution based on KB 916766, this did not resolve the error.
Only possible resolution I found was to disable UAP, reboot and retry the install. This will be done as a last resort, but any other suggestion will be appreciated.
I want to change from cal user license to processor license in sql 2005. How do I do this? Do I need to reinstall sql? Assuming I need to reinstall, will my settings be gone?
I would like create/modify/update the alias SQL server name on the SQL 2005 version.
I had a database mirroring on one base, and when the primary server fail, i would like my secondary alias become as the primary. I need this because of the application whom use my SQL server.
I tried with
sp_dropserver <old_name> GO sp_addserver <new_name>, local GO
But this way doesn't work...
Anyone got an idea to change alias SQL server in transact?
I have an existing VB6 application which uses an Access 2003 backend datafile, however, I would now like to modify this to use an SQL 2005 datafile, but I don't know how to go about this or the implications when the application is installed on another machine.
The code I use for accessing the Access 2003 file is below, I want to convert this code to access an SQL 2005 datafile instead, can anyone give me some pointers on the best way to go about this, or code sample.
Dim rst As ADODB.Recordset Dim dbs As ADODB.Connection
Set rst = New ADODB.Recordset Set dbs = New ADODB.Connection
I have created a DB and up until yesterday I have been using SQL commands to add/remove tables, columns, constraints etc. However today when I attempt to modify the datatype of a column using
alter table Person modify Gender nchar(2)
I get this error
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'modify'.
I can still add colums but get the same error if I try to drop them and I can amend the tables in any way via the Design view so I don't think it's permission related.
The smallest downloadable version seems to be 53MB. Is there any way to create a smaller version to use as an embedded DB for an application? I only need it to be single-user, for what that's worth. I'm thinking more in the 10MB to 20MB range.
I have bought SQL Server 2005 Standard Ed. which will be used only to host databases for different applications like WSUS, McAfee Protection Pilot and CA Brightstor ArcServer. I do not have intention to create corporate applications using SQL Server.
In fact, I am using the SQL Server as a "multiple MSDE database server"...
My question is what are the minimal components between the following features to install (as I do not not really what they are doing) : -SQL Server -Analysis Services -Reporting Services -Notification Services -Data Tranformation Services -Workstation Components
The transaction log takes up a lot of space on my database, and even after I try truncating the log, doing a transaction log backup, and then shrinking it, I am not allowed to reduce the size of the transaction log to less than 250MB. Is there some reason why this space is required?
Hello,I'm upgrading from SQL 7 to SQL 2000 on another box. To minimize thedowntime I would like to1) backup my sql 7 database,2) copy it to the new box with SQL 2000 already installed,3) restore the database on the SQL 2000 box,4) Shutdown my sql 7 database,5) Copy the transaction logs to the SQL 2000 database,6) Restore the transaction logs to the SQl 2000 database,7) Bring up SQL 2000.My only concern with this is restoring the transaction logs that werecreated on SQL 7 to SQL 2000. Do you know if I can do this?Do you see any (other) problem(s) with my plan.Thanks, Scott
We are using sqlserver2005 at our liveserver. Due to some third party attacks which caused loss of data, we changed the sql user permission to only read,write and execute. Now, some of the sps in the db contain code to insert into identity column with line
SET IDENTITY_INSERT [tblName] ON insert stmts... SET IDENTITY_INSERT [tblName] OFF
This throwing error as
Cannot find the object "tblName" because it does not exist or you do not have permissions.
Which minimal permission can be given to get the above code work with identity insert on/off? We have removed the dbo permission due to external attacks.
Here a code for finding all minimal loops (cyclic paths) in a graph with vertexes of degree >= 3. Almost obviously that before seeking for loops we should eliminate from the graph all its vertexes of degree < 3 (degree of a vertex is the number of edges outcoming from the vertex). Note: there are no any 'parent' - 'child' nodes here. All vertexes are absolutely equitable. if object_id('g3')>0 drop table g3 if object_id('g3x')>0 drop table g3x if object_id('g3y')>0 drop table g3y if object_id('g3l')>0 drop table g3l GO create table g3y(v1 int, v2 int) -- ancillary table GO create table g3x(n int, v1 int, v2 int) -- ancillary table GO create table g3l(nl int, v1 int, v2 int) -- table for storing of 'detected' loops GO create table g3(v1 int, v2 int) -- table of test data with pairs of adjoining vertexes -- each vertex is named by an arbitrary number GO insert into g3 select 2, 3 union all select 2, 4 union all select 1, 4 union all select 3, 5 union all select 5, 6 union all select 1, 6 union all select 4, 7 union all select 6, 8 union all select 3, 9 union all select 1, 7 union all select 2, 7 union all select 1, 8 union all select 5, 8 union all select 2, 9 union all select 5, 9 ----union all /* select 2, 13 union all select 3, 13 union all select 13, 14 union all select 12, 14 union all select 12, 15 union all select 11, 15 union all select 11, 13 union all select 10, 11 union all select 10, 12 union all select 10, 14 union all select 10, 15 */ GO insert into g3 select v2, v1 from g3
declare @i int, @n int, @v1 int, @v2 int set @i=1
while 0=0 begin set @n=1 truncate table g3x truncate table g3y select top 1 @v1=g3.v1, @v2=g3.v2 from g3 left join g3l on (g3.v1=g3l.v1 and g3.v2=g3l.v2)or(g3.v1=g3l.v2 and g3.v2=g3l.v1) where g3l.nl is null if @@rowcount=0 break insert into g3x select @n, @v1, @v2
while @v1<>(select top 1 v2 from g3x order by n desc) begin set @n=@n+1 insert into g3x select top 1 @n, v1, v2 from g3 where v2=@v1 and v1<>@v2 and v1=(select top 1 v2 from g3x order by n desc)
if @@rowcount=0 begin insert into g3x select top 1 @n, v1, v2 from g3 where v2 not in (select v1 from g3x union all select v2 from g3x) and v1=(select top 1 v2 from g3x order by n desc) and not exists (select 0 from g3y where g3y.v1=g3.v1 and g3y.v2=g3.v2) if @@rowcount=0 if @n>2 begin insert into g3y select v1, v2 from g3x where n=@n-1 delete from g3x where n=@n-1 set @n=@n-2 end else begin insert into g3l select 0, v1, v2 from g3x break end end else begin insert into g3l select @i, v1, v2 from g3x set @i=@i+1 end end end select * from g3l order by nl Below is what we get:
7 5 9 7 9 3 7 3 5 Of course, in general case not all found by the code loops are minimal. But this is exactly my approach: firstly find any possible loops (avoiding excessiveness!!), then, in WHILE loop, try to mark out minimal loop(s) from intersection of two non-minimal loops... seems it will be an interesting t-sql job.
I'm currently working on a project at work to test the effects of database compression, trying to obtain measurable data on the impact of the compression on other server resources, and therefore whether the reduction in space used is worth the extra overhead. This has involved taking a trace of a production customer's workload for a period of time and replaying it against a backup using Distributed replay in synchronised mode.
I'm then taking a trace of that replay, as well as using perfmon to record useful data about the server, before and after compression is enabled. Finally, I'm loading the traces into a tool called Qure to analyse the impact of the compression on reads, writes, CPU, overall duration etc.
What I'm finding is that even across 2 different 'baseline' runs, which are replaying the exact same workload against the exact same database, performance etc differs to a significant enough degree that it calls into question the validity of the test. I can only put this down to the fact this server is on a VM, which is affecting available resources, which in turn affects execution plans the workload is generating and causes different replays of the same workload. I'm therefore looking at doing this on a standalone server, but I still can't be sure the differences will go away.
How to make tests such as this as similar as possible on multiple runs, when elements outside of SQL Server are in effect out of my control?
I have a process that restores a production DB, overwriting the existing copy each night. I'd like to keep the solution "up" for as long as possible. And this'll be more important if I want to update it in the day (where there are more queries) too. The nature of queries thrown at the system is that there are about 20 per hour, it's underpinning a reporting system, it's not an OLTP system.
It seems to me I could restore the fresh DB copy into a holding DB, then rename it to the production DB name at the end of the process. The rename process should be pretty much instant.
But I need to think about detecting and waiting for queries to complete on the prod DB, before removing/demoting it (actually, I though to rename it, then reusing it as the next copy to update).
I have a table (named table1) with 20million rows. It takes around 11 minutes to apply the primary key to this table. There are some tables with over 100 million rows so based on the previous time if my calculations are correct it will take close to an hour apply this primary key for tables with around 100 million rows.
My current solution is to create another table (named table2) with no indexs or primary keys. Pump over only like 5 days worth of data, then apply the primary key. Then have a script that will eventually populate table2 with the rest of the data gradually. When I say gradually I mean like insert like every 100k per hour or something. Keep in mind this table2 is heavily updated with new records.
I am trying to migrate our processing from command line based scripts and foxpro to SQL so I need to run the SSIS packages using dtexec. I copied the dtexec file and a few dll's that are missing to our production servers but i cant execute the packages. I dont want to install the full client tools (particularly managment/business inteligence studio) on our production servers due to the overhead and limited system disk space.
Can somebody tell me what the minimum install would be so I would be able to run SSIS packages using the dtexec or dtexecui tools? I would also like to install some of the other command line client tools like osql etc.
I understand that minimal logging can occur on a non clustered indexed heap as long as [URL] ...
*not replicated
*tablock is used
*table is empty
The following test seems to contradict this
In the test I create a non indexed heap, insert some record and check the log, then repeat the test on an indexed heap.
The results suggest that even though the conditions for minimal logging into a indexed heap are met, minimal logging is not happening although it does happen on an non indexed heap. What am I doing wrong?
CREATE DATABASE logtest GO USE logtest GO CREATE TABLE test (field varchar(100)) GO CHECKPOINT
- restore a backup of a 3rd party database onto one of our servers - this has no users that I can use - there is some ETL processing so we're using Control-M to manage the process - create a database user and grant it db_reader.
I'd like to do this without granting any users elevated privileges if possible.
What I've done so far is grant the Control-M user (this is a domain user) dbcreator rights and made it owner of our copy of the database that is being refreshed.
The refresh is completing, but Control-M is not able to log onto the database to create the user.
What is the best way to accomplish this task without granting the control-m user sysadmin rights?
Would I be able to do it if I used a SQL Agent job for the restore and user creation?
I am trying to debug a sp, I did it like a month ago and now i am getting msg:" T-SQL execution ended without debugging. You may not have sufficient permissions to debug"
I am trying to debug a sp, I did it a month ago and now i am getting msg:" T-SQL execution ended without debugging. You may not have sufficient permissions to debug"
The server is a remote server
ANY help PLS!!!
The dba says nothing has changed regarding my user log in or domains. I am a sysadmin user (he tells me) , I have looked up info but really dont understand any of the stuff, (i am a super dummy! and just started this job 5mo ago, and learned from reading a book, so i dont have any great understanding about domains or anything. Pls help
I installed sql 2005 a while back. Then I recently found out my file system was fat32 (I don't understand why the hardware people did this...) and I had to convert to NTFS. Naturally the sql service no longer worked so I uninstalled inorder to reinstall now I can't reinstall it I keep getting this message
native_error=5039, msg=[Microsoft][SQL Native Client][SQL Server]MODIFY FILE failed. Specified size is less than current size.
In SQL/2000 EM I can go to a user Database, expand the Users, double click a user and click Permissions to see everything a user has permisson to in the database. How can I get the same information in Management Studio in SQL/2005? Is there an overview of this process in BOL 2005?
I am just migrating to SQL Server 2005 and I am having difficulty figuring out how to do some tasks that were easy under SQL Server 2000. Specifically, I am not sure about object permissions.
(This is what I did for SQL Server 2000) For database access by my web application, I added a SQL login for the IIS_WPG group. I then added a database user (name ASPNET) associated with this database login and give it only datareader and datawriter privileges. I would then 'double-click' the user, which would bring up the list of securable objects. I would then click the 'execute' permission for all of the user-created stored procedures. [done]
For SQL Server 2005, I am not quite sure what to do. It seems to me (based on what I see after importing the tables), that a schema should be used, but I do not see any explicit permissions in the schema. On the other hand, if I select each stored procedure and look at its properties, I can see that the ASPNET user has execute permission.
It seems inconceivable that the only way to configure permissions is to modify each SP by hand. This would take more than 10 times as long as the SQL Server 2000 method of assigning permissions.
So, my question is this: How can permissions be assigned 'wholesale'? (i.e., some method akin to the SQL Server 2000 method that does not require setting permissions on each individual object individually.)
Of course, if you care to suggest a better way to do this, I'd love to hear it!
Hi. I created user: "Reporter" This User is set as my anonomoys login user in IIS 6.0. I gave him permissions on both the database server & the Report server, however I keep getting an error. "The permissions granted to user 'SRVRReporter' are insufficient for performing this operation. (rsAccessDenied).
I inherited the task of syncing up these new reports & I'm trying to follow what the last guy did (not exactly though, that's why I created the new user)
I gave the new user login rights on both servers as well as access to the SQL 2005 database. I have the identity impersonate="true"
Can anyone out there tell me what permission I need to grant to get my reports to run?
Hi, I have a table in my database where I want the Insert/Modify permissions to only administrator or (User X). Remaining users can just read the data. How do I set this in sqlserver 2005 database? I can right click Table->properties->Permissions and add specific permissions to admin. But how would i deny permisssions to all others? I cannot add each login to deny permissions. Thanks,
I have been asked to grant a Windows group Full access to all tables under our Sandbox Schema. This will allow these users to do anything to the tables under this Schema.
I created the Windows Group (Sandbox Users), created the login in SQL, created the user in the database that is tied to the Windows group, then ran GRANT CONTROL ON SCHEMA::[Sandbox] TO [Sandbox Users].
I have verified that the users are in the Windows group, but they state that they still can not delete tables under the Sandbox Schema.
