I'm running into an issue with a user with restricted rights being able to access a local SDF file. The user has Modify right to the folder (and the file), but cannot access the SDF if it was created by someone else.
The only two workarounds I've found are: 1) If the I delete the SDF and then the restricted user creates the SDF they can then access it. 2) If I grant Full Control to the folder then the restricted user can access the database.
Is this by design? Are there programatic changes that can be made to enable access?
All users who login to the box (Windows XP) need to be able to access the same SDF. The SDF is stored on the local machine.
I have a user that should only have the rights to view the jobs and database properties within Enterprise Manager. I am not sure how to do that. Can you please help? Thanks.
Hi, I am using SSRS 2005. Created several reports on the server where SSRS is installed. In addition I managed to develop a few more reports on my work station and then deployed the reports to the server. From my local machine I can brose to http://servername/reports and view/run the reports. Now I would like to find out if/how others can view some of the reports. How/where do I set rights...? Thanks
Hi, I want to give 'Execute' permission for all the sp to a particular user. Any straight way of doing this then to go to each sp and give him permission for every sp. TIA.
I need some advice with a msSQL 2005 databaseI'm creating a administration program in vb.net based on a new msSql db. This programme is involved with customeradmin, facturation, products, sales,...by example: Some employees don't has anything to do with product, so they don't need the rights to delete, create or edit it.The question is how can i resolve this problem, because i don't find any good solution. The rights are for every employee different, and can be changed by a admin panel. The admin can give a employee specific rights for every part of the programmeso how can we give a user certain rights when he is logging in into the program.thanks, BoardD :S ;)
In the login properties on the Database Access tab, a user's name saysdbo instead of his name. I do not want him to have dbo permissions. Icannot drop the db_owner permissions that he has on the databasebecause I get the error "Error 15405: Cannot use the reserved user orrole name 'dbo'."How do I remove these permissions from this user?
I'm new in the SQL Server Express Configuring Stuff and I've a problem or more exactly I don't have any idea about configuring an secure and hacking save user for a SQL Express DB.
What server roles/server permissions/endpoint permissions/database permissions are neccessary to have an rw access SQLE user? Any articles available? Any suggestions?
Scenario is a Personal Webpage for my Family & Friends located in my house
- ASP.Net Starter Website: Personal Website
- 2 MDF DB's: ASPNETDB.MDF & PERSONAL.MDF (included with the Starter Website)
- Development on DEV PC; Deploy on SERVER PC
HW: - SERVER PC with Win2k (fully patched) - DEV PC with Win XP Sp2 (fully patched) - LAN 100Mbit w NAT FW - WAN Cable Modem 128/1500
SW: - APACHE 2.0.55 with latest aspnet_mod - ASP.NET 2.0 - SQL Server Express - SQL Manager 2005 Lite - MS Web Developer Express
What I've done so far: - APACHE: configured & working - aspnet_mode: configured & working - SQLExpress: configured & working - SQLBrowser: configured & working - TCP/IP Protocol for SQLE: configured & working - Mixed authentication for SQLE: configured & working - ASPNETDB.MDF: attached, configured & working - PERSONAL.MDF: attached, configured & working - SQL Manager 2005 Lite: configured & working - SQLE users: created one for ASPNETDB and one for PERSONAL (with SQL Manager Lite; Sorry MS!!! your SQL Server Mgmt Studio Express sucks!) - WEBSITE: changed connection string from local to remote
(local doesn't work, because the LocalSystem User on Win2K, which is used by the SQLServer, has no User Profile. Also you cannot change the Service User, so it won't work with the local connection string, but with an attached and remote accessible DB it works just fine) - WEBSITE: runs locally and remotely on DEV PC with remote DB's and custom users.
But I'm concern about the two users I've created, to access the two DB's. I've no idea, what rights/roles aso they need to use the DB for remote (for development) and local (for production) access and to be secure?
How can we allow users to access a MS SQL Server OLAP cube but restrict the level of data that he might see? I mean, if there is a cube with data for three different countries. How can one user be restricted to see data pertaining to only one of the countries and not the other two countries?
I'm rather new to the MS SQL Server development in general and especially to its data security architecture and features - I'd like to know if it is possible for end-user to retrieve/update(!?) the data using a SP which executes on a table for which she/he doesn't have any privileges.
What rights do users require to be able to use SQL Server Express?? I receive errors when the user doesn't have full admin rights. Our users cannot have admin rights all the time on their PCs. Errors below:
"Create failed for Database 'TestDatabase'. (microsoft.SqlServer.Express.Smo) Additional information: An exception occurred while executing a Tranact-SQL statement or batch. (Microsoft.SqlServer.Exress.ConnectionInfor) Create Database permission denied in database 'master'. (Microsoft SQL Server, Error: 262)"
Hi, How to ennumerate the Rights of an User for all the tables [Select/Insert/Update/Delete] in a database or how to ennumerate/list all the Table rights for a particular user in a database? By User, i mean the Login names [like bill, sam, sa] and not dbowner, public, etc. thanx in advance.
Does anyone have a SP which will output all users and their permissions (dbo), (read), (write) for each database for SQL 2k? I'm trying to find a way to simply automate this so I can output the data on demand for auditors. I'm currently checking each users permissions on each database through security/logins :(
Does anyone know of a quick way to audit all users in a database and display their rights and permissions on a table level. I would hate to have to do it one user at a time. There has to be an easier way.
I'm going through a Sarbanes Oxley audit and need to provide them this information.
Could someone please help me for SWL backup restoration and db by user
I restored SQL 7.0 database to SQL 2000. ( by creating empty db on SQL 2000 and restored from SQL 7 backup) -- restore ok.. I need to use same SQL user which is admin for DB on SQL 7, for SQL 2000 also. ( DB user is sql user not domain user) I put mixed authentication mode ( windows and SQL) in SQL 2000 enterprises manager security tab setting. I can see DB user is available in DB user list on restored DB but can not access DB when I try to access from query analyser I tried to create new login with same name as it was in SQL 7 and tried to give full admin access on SQL 2000 enterprise manager but I get error 21002:[ SQL DMO] User 'user' already exists.
So there are 2 connections: one to a excel file, and second to SQL using sa account. I am working on my account belonging to Administrators group.
SQL Server and Excel file are on my development machine. Everything works ok when I run package from file system using DTExecUI.exe (in context of my user belonging to Administrator group). But when I have created job to execute this package problem with security occured. Even assigning local account on which SQL Server and SQL Server Agent works to Administrator group do not help. I have simulated what can happen when running DTExecUI.exe utility in contex of different user. I have used Administrator (I start it using "Run As..." from context menu in Explorer"). And what occured, SSIS package can not login to SQL Server using sa user!
1. Why running DTExecUI.exe in context of different user impact login to SQL on sa accout? If it were Windows Integrated I could understand it.
2. Especially if the different user is also Administrator!!!
I have found solution for running a task in SQL Agent - SQL Agent has to work on the same account on which SSIS package was created. But this is crazy when I would like to deploy it to a production server.
BEGIN TRANSACTION @Tran1 ¦¦¦¦¦. ¦¦¦¦¦¦ ¦¦¦¦.
INSERT INTO [tabloA] (, ,) SELECT ,, FROM [tmptabloA] WHERE ......
¦¦¦ ¦¦¦ ¦¦¦.
DELETE FROM [tmptabloA]
COMMIT TRANSACTION @Tran1
When user [nuran] execute the procedure sp_yordam by a VB program, the procedure use [dbo].tmptabloA not [nuran].[tmptaboA]. If there are data in the [dbo].tmptabloA, the procedure insert data to [dbo].tabloA from [dbo].tmptabloA. But when I checked user name in the procedure during execution, the user was [nuran].
If I write the procedure like that:
(2) create PROCEDURE [dbo].[SP_tmpSil] AS
declare @tablo1 as varchar(50), DECLARE @sil as nvarchar(max) select @tablo1='[tmptabloA]'
And it executed by user [nuran],then it used the correct table [nuran].tmptabloA
Is there any way to use users table in an stored procedure without using the user name : (3) create PROCEDURE [dbo].[SP_yordam] AS BEGIN
BEGIN TRANSACTION @Tran1 ¦¦¦¦¦. ¦¦¦¦¦¦ ¦¦¦¦.
INSERT INTO [tabloA] (, ,) SELECT ,, FROM [nuran].[tmptabloA] WHERE ......
¦¦¦ ¦¦¦ ¦¦¦.
DELETE FROM [nuran].[tmptabloA]
COMMIT TRANSACTION @Tran1
I don't want to use (2) and (3) code methods, I prefer to use (1) script. Is there any compilation method, or any aditional way for using script (1) with correct user rights?
When I restore DB from testing to production, we want to remove extra access rights granted to public group. Is there a simple way to query to find out for which objects (table, view, sp, fn) that public group were granted select, delete, update insert, or execute rights?
My objective is to write a sp to remove all user assigned rights to public group (role), but not to deny any rights. How to do it?
To use Reporting Services as a rendering engine I want to configure a local user on the server that has only the minimum set of permissions and user rights. The server is W2K3 SP2 and SQL 9.0.3200.
In particular, this local user has been removed from the local "Users" group and so is the "Authenticated Users" built-in group. In Reporting Services, it is mapped to a role that only has the "Execute Report Definitions" task permission.
Then, following the details in http://support.microsoft.com/kb/812614/ (Default permissions and user rights for IIS 6.0) I added all file security and local user rights required for "Users" and also granted and propagated "Read&Execute" on the "Reporting Services" folder and verified this using "Effective Permissions" on the ReportService2005.asmx file.
However, I still get 401 Unauthorized, also after a complete restart of all related machines and services.
Once I add the user or "Authenticated Users" back to "Users" everything works fine.
What permissions might I be missing? Where could I find those permission requirements documented?
I tried analyzing the 401 using auditing file and object access security but to no avail. There are no Failure audit entries in the Security log.
How can I investigate the minimum permission set?
What is the risk of leaving the user in the "Users" local group?
We have a SQL server with many legacy DTS packages. sa and Admins can open them and change them then save them but we need to allow the DTS people (Developers) the rights to save the package after they have opened it and modified it.
I am facing a problem which is based on the restrictions on the domain of the customer. After deplyoing the report on the server we are getting the following error message in the report manager after executing (clicking) a report link:
Logon failed.
Logon failure: the user has not been granted the requested logon type at this computer. (Exception from HRESULT: 0x80070569)
I googled for that and it seems to be an authentication issue where some user / account is not granted to log on as service / locally, but the problems is better described than the solutions. Did anyone faced that problem so far ? Which account has to be granted what priviledges or permissions in Windows. We are using a SQL Server 2005 / Reporting Services 2005 running on the same machine whereas the virtual directories Reports and Reportserver are running in a separate Application Pool
Basically I want to set chain up the rights so that the anonymous web user IUSR_ .. can execute the new .NET subs, functions etc in the assembly, just as the anonymous web user can execute Stored Procedures when granted. In this way, it should be possible to call the .NET assembly just as classic stored procedures from ASP/ASP.NET.
I have written a .NET function which I can successfully execute if I log on to the database as an administrator by sending this T-SQL query; it returns the result of a given string:
select dbo.CLRHTMLString('abc')
The scenario is now to try to grant access to this assembly for a different role (webuser), which the classic IUSR_MYSERVERNAME is a login of, so that I can call the .NET Assembly when I am authenticated as the anonymous web user (e.g. via ASP, etc.).
To test access, I created a login (webusertest) for a user (webusertest) in the same role (webuser) on the database. But when I use this login, which supposedly has the same rights as the IUSR_, execution right is denied:
EXECUTE permission denied on object 'CLRHTMLString', database 'adt_db', schema 'dbo'.
Note: The 'webuser' database role has Execute permission on the Assembly.
I have also tested this from my actual web page, with the following results: (1) IUSR_MYSERVER member of db_owner role: Web page has right to call assembly. (2) IUSR_MYSERVER not member of db_owner role: Web page does not have right to call assembly.
Further test results: (3) Function can be called when making the user "webusertest" member of the "db_owner" role, which is too much rights to grant for the anonymous web user.
(4) When adding the user 'webusertest' to get 'Execute' permissions on the assembly, it does not get added. After clicking OK, there is no warning message, but when opening the Assembly Properties -> Permission dialog box the same time, the 'webusertest' user does not appear in the list.
The DBA at our location is demanding local admin (windows) right's to the box so he can function. Right now when he logs in i have given him right's to the inetpub directory, sql directory, i have set him as a sysadmin on sql2005 and gone into the http:\localhost eports and set him up as a system manager and under site priveledges set him as a sys admin. When he tries to login and configure the report server he gets the following error:
Title-Reporting services configuration manager
Error-There was an error refreshing the UI. bla bla bla
A WMI error has occurred and no additional error information is availiable
Title-Reporting services configuration manager
Error-There was an error while switching panels. The most likely cause is an error retrieving WMI properties. bla bla bla
A WMI error has occurred and no additional error information is availiable
then when he's in sql server 2005 surface area configuation
Title-Surface Area Configuration
Error-Access denied (system.management)
Is there any documentation or anythign anyone can tell me that i can do to give this DBA full access to configure and admin the SQL portion of his system without giving him admin rights to the OS???
Please help!!
Thanks for any time anyone has taken to review this thread!!
This is my first time to deploy an asp.net2 web site. Everything is working fine on my local computer but when i published the web site on a remote computer i get the error "Failed to generate a user instance of SQL Server due to failure in retrieving the user's local application data path. Please make sure the user has a local user profile on the computer. The connection will be closed" (only in pages that try to access the database) Help pleaseee
Hello, I need to create a sp that allows a user(not sa) to reset passwords using sp_password. The part that I'm stuck on is how to login within the proc so that the user(not sa) can exec the sp_password as sa without having to give the user sa rights. I don't mind hard coding the sa password with the proc but I can not give sa password to the users. Do I need to somehow alter sp_password for this to work?
Need to give a user permission to add logins and users to a database. Have tries to alias the user to DBO but it doesnt work. Is there a way to do it other than reassigning DBO permissions to the user.
Is there a way in SQL server to grant "SA" rights to non-SA users for certain commands.
I know there's a way to do this in Sybase by creating a password protected role and then activating it within a stored procedure.
Thus, the specific right is only active for the brief duration of the stored procedure - which runs the particular command to be granted. The role is de-activated at the end of the stored procedure.
I have a basic question regarding rights. What level of rights do Ihave to have to grant another user update rights? I don't want togive everyone owner rights. Can a person with update rights grantanother person update rights?Thanks.
Hi everybody, The below I posted on SQL 2000 Forum about a week ago. Any new thoughts................ I would like to get an input from as many people as possible on the following: In our organization DBA is responsible for 5 servers ( currently NT 4/SQL 7)and is a part of group of a 5 people including manager and 3 developers. DBA currently has a FULL access to every server. In a few months we will be replacing the existing system with Windows 2000/SQL 2000. LAN group will give to DBA only a read rights for the Windows 2000 environment, saying that the AUDITORS, both internal and external, require that. In other words, if DBA needs to run a command prompt, move files from one directory to another in Production environment, he has to fill the request to LAN, so LAN group would do that. So I guess the main question(s) is: What is the degree of involmment of DBA with Operating system? Is DBA suppose to be an NT administrator ( I dont think so, since DBA has a lot of other thing to do? If DBA accidently makes an unwanted changes to the Operating System, who should be blamed for ( not personally, but in more general terms) and would it be an extra argument to take write rights away from a DBA? What auditors saying about that? Thanks a lot in advance, Andrei
