Hi All,
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.
TIA,
Shamil
Hi:
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?
Any suggestion will be appreciated.
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
Hi everybody,
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
I gave a developer rights to the Public role on a SQL Server 2000 database. The Public role only has explicitly set select rights to the system tables and one user table. There are no other explicit rights set. The developer was able to open a table that had no rights set in enterprise manager and change data. Is this possible?
View 1 Replies View RelatedI dont know how to arrange situation when application enduser needs to access data in two databases of mssql server concurently in those circumstances that access rights to the data should be restricted by password protected role (whose password is not known to the end user).
Detailed description of problem:
So far there was an application, that manipulated its data, saved in mssql server's database. End user authenticates to application by his (mssql server's) login name and password. The application authenticates the user by connecting to the database with the given name/password credentials, and then the application sets application role with hardcoded name/password. Thus application role sets the access rights for consequent end user's requests, delivered via application to the database server.
The goal is that end user cannot manipulate application database data when connects to the database by other means (e. g. via SQL server Manager), because he does not know the application role's password.
Now suppose that there are two applications (A1, A2), both using the same model for access restrictions. Each of them has its own database (A1DB, A2DB) and its own application role (A1R residing in A1DB, A2R residing in A2DB). End user (login) X can manipulate A1DB data when connects via A1, and A2DB data when connects via A2, and NO data when connects by other means.
Finally suppose that some subset of A2 data (let's say one table) is useful to see also via A1 application. There is no problem to add to A1DB view, that shows data from A2DB table together with A1DB tables. But when the user is connected via A1, he cannot see the data, because query on A1 view fails (user has not access rights on A2 data).
The access rights for A1 enduser cannot be set by no means i know because:
1) I cannot set the rights via public (guest) access because in that case they will be accessible to any users connected by any third party products, which is supposed to be security hole.
2) I cannot set the rights via dbuser or dbrole privileges, because they will not work when connected via A1 application (setting the app role suppresses the db privileges)
3) I cannot set the rights via application role because two application roles cannot be set concurrently.
4) I cannot abandon using application roles mechanism and use database roles mechanism, because db roles cannot be protected by independent password (not known to the enduser).
Please can anybody review my problem and either find the mistake in my approach, or propose other solution? So far I suppose the problem is my ignorance, because I am not great mssql expert.
We have been working on an application that will be using a forms-authenticated report server (RS2005) as a reporting back-end. Using the reporting services web service I have been able to assign permissions to objects in reporting services no problem. The issue is that each user needs to be added to the System User role to be able to use the report builder properly. I can't seem to find a way to do this programmatically. Any idea?
View 1 Replies View RelatedHi All,
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
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.
Thanks,rlw...
Hi everyone,
I try to add a db_role or a user to all my databases with one script. Although parsing doesn't report any problem I get a Syntax error during execution.
I first select the database names into a #temp table which has two columns, ID and dbname.
After that I use the following code:
DECLARE @Count smallint
declare @dbVarchar(20)
SET @Count = 1
WHILE (@Count <=(SELECT MAX(ID) FROM #temp))
BEGIN
Exec ("USE @db")
EXEC sp_adduser 'test'
EXEC sp_addrolemember 'my_role', 'testrole'
SET @COUNT = @COUNT + 1
SET @db = (SELECT dbname FROM #temp WHERE ID = @Count)
END
It seems that the "Use @db" part has no value for the variable @db.
Does anybody knows how to solve this?
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.
Hi All,
How can I give the user view rights only?
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 ;)
View 1 Replies View RelatedDoes anyone know if it possible to make a specific table invisible to auser when they are in Enterprise Manager?
View 1 Replies View RelatedIn 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?
View 3 Replies View RelatedHi, SQL experts.
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?
Thanks for any help, ideas and suggestions.
Alex
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?
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 :(
View 3 Replies View RelatedHello everyone,
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.
Thanks in advance for help.
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.
Kind Regards
Sunod
I have a small SSIS package which:
- drops existing table in Excel,
- creates a new one in Excel,
- copies data from SQL to newly created sheet.
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.
Please help.
Przemo
Hi,
I have a problem with sp execution.:
objects of [dbo]
Tables of [nuran]
Grants of [nuran]
[dbo].tabloA
[nuran].tmptabloA
Select,insert, update on [dbo].tabloA
[dbo].tmptabloA
Deny for [dbo].tmptabloA
[dbo].sp_yordam
Grant for executing [dbo].sp_yordam
(1)
create PROCEDURE [dbo].[SP_yordam]
AS
BEGIN
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]'
SELECT @sil = ' DELETE FROM ' + @tablo1 + ';'
EXEC (@sil)
END
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?
Thanks a lot
Nuran
What's best practice (security wise) in granting a user access right to edit say the description of a job.
View 2 Replies View RelatedTo 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?
Any help appreciated.
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.
Thanks
I'm trying to install SQL Server 2005 Express on a Windows 2000 server, but I'm getting the following error message:
"Failure setting security rights on user account SQLServer2005BrowserUser${computerName}"
Can anyone help me please?
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
Thanks,
Thomas
Hi Everybody,
The end users are using VB Applications, there they will be entering datas. Those datas will be stored in the SMS Database.
My Problem is through which Roles (Fixed Server Roles or Database Roles) I should attach these end users. If it is a Fixed Server Roles, Other than sysadmin role in which role I should attach this end user. Like that other than DB_Owner in which role I should attach these end users to the Database Roles.
Can anyone guide me please.
thanks,
Srinivasan.
Dear all,
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.
Thankful for any advice on this matter.