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?
View 7 Replies
ADVERTISEMENT
Nov 3, 2010
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?
View 9 Replies
View Related
May 21, 2014
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.
View 3 Replies
View Related
Feb 12, 2013
Permissions on SQL Server Agent.
I would like to assign the permission to user ,who can edit all SQL agent job(even own by others) without assigning the sysadmin role.
View 14 Replies
View Related
Feb 19, 2007
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.
View 5 Replies
View Related
Aug 15, 2007
Received the following error while installing SP2
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.
Many Thanks
View 3 Replies
View Related
Nov 22, 2007
Hello All,
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?
Sorry, I'm new to sql. Thank you!
View 3 Replies
View Related
Oct 30, 2006
Hello,
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?
Thanks for your help!!!
View 1 Replies
View Related
May 19, 2007
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
dbs.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
strNetworkLocation & "MyDB.mdb; JET OLEDBatabase Password=MyPassword"
With rst
.Open "SELECT TOP 1 * FROM tblProject WHERE pjProject = '" & _
lstProject.Text & "'", dbs, adOpenKeyset, adLockOptimistic
strDrawingRef = !pjStartupDrawing
strProject = lstProject.Text
frmMain.OpenFile
.Close
End With
Thank-you in advance for any help you may provide.
Mandy
View 4 Replies
View Related
Mar 6, 2008
I am tearing my hair out with this!!
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.
Any ideas greatly appreciated.
View 1 Replies
View Related
Jun 16, 2005
does anyone know how to modify table structure in sql server management console(2005)? thanks
View 1 Replies
View Related
Jul 22, 2006
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.
View 3 Replies
View Related
Jul 25, 2006
Hi,
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
Thanks in advance
Eric
View 4 Replies
View Related
Jul 27, 2000
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?
View 1 Replies
View Related
Jul 20, 2005
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
View 1 Replies
View Related
Jun 12, 2014
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.
View 2 Replies
View Related
Oct 1, 2003
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:
nl v1 v2
----------- ----------- -----------
1 2 3
1 3 5
1 5 6
1 6 8
1 8 1
1 1 4
1 4 2
2 1 6
2 6 8
2 8 1
3 4 7
3 7 1
3 1 4
4 3 9
4 9 2
4 2 3
5 2 7
5 7 4
5 4 2
6 5 8
6 8 6
6 6 5
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.
View 17 Replies
View Related
Apr 8, 2014
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?
View 0 Replies
View Related
Oct 12, 2015
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).
View 5 Replies
View Related
Sep 11, 2007
Hi,
We are using SQL Server 2005 Management Studio.
I created a Minimal User in an application DB. The user will access tables through stored procedures.
I do not want this user to view any other objects including objects in the Master DB.
I can prevent the minimal user from viewing objects from our application DB.
How do you prevent the minimal user from viewing objects in the Master DB?
Thanks.
Tim.
View 7 Replies
View Related
Jul 30, 2015
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.
View 2 Replies
View Related
Jun 15, 2006
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.
View 8 Replies
View Related
Jul 9, 2014
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
[Code] ....
View 2 Replies
View Related
Apr 29, 2015
What I want to do is :
- 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?
View 1 Replies
View Related
May 4, 2008
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"
The server is a remote server
ANY help PLS!!!
View 4 Replies
View Related
May 5, 2008
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
View 1 Replies
View Related
Jun 15, 2006
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.
I'll try to post the full log in a new post.
View 11 Replies
View Related
Jul 7, 2006
Hi guys
Am i able to deny the SA account access to a specific database?
thanks in advance for your help.
TheGing
View 4 Replies
View Related
Nov 2, 2007
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?
Thanks in advance!!!
DeWayne
View 3 Replies
View Related
Feb 22, 2006
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!
Thank you.
View 3 Replies
View Related
Nov 20, 2007
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?
View 3 Replies
View Related
Feb 7, 2008
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,
View 5 Replies
View Related
Aug 15, 2007
All,
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.
Anyone have any ideas?
Thanks,
Justin
View 5 Replies
View Related
