Assigning Rights To User
Apr 5, 2001Hi,
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.
ADVERTISEMENT
Assigning User To A Database
Aug 11, 2007Hi
I am using SQL SERVER 2000. Until now my application used the default user "sa", but now the illigal access to my database make me move to a more secure login.
i am new to this concept.
i need to create a login, which i am successful in creating, but my problem is
I need to allow only this user to access my database and no other user should login my database.
please can any one explain how to do this.
its very urgent.
regards
James Alvin
User Rights
Nov 28, 2005Hi 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.
User Rights
May 17, 2007Hi,
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
User Rights
Dec 7, 2006I'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...
Limited User Rights
Nov 16, 2005Hi All,
How can I give the user view rights only?
User Rights System
Feb 22, 2007I 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 RelatedMS SQL Server - User Rights
Jul 23, 2005Does anyone know if it possible to make a specific table invisible to auser when they are in Enterprise Manager?
View 1 Replies View RelatedCannot Remove Dbo Rights From A User
Jun 16, 2006In 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 RelatedWhat Rights/roles To Use For A DB User?
Feb 20, 2006Hi, 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
Problem In Assigning Default Schema To Database User
May 6, 2008Dear All,
I am using SQLServer 2005, I have setup a login user "User1" and next I setup database user using the same username and login name. After that I create a new schema "mySchema" and make "User1" as the owner of the schema. To "User1" I assigned the default schema to "mySchema", so far its working fine. But when I open the user's property window (dialogbox) the default schema always gets reset to "dbo".
What could be the problem here? Please help me if there is any solution to get the right schema which I assigned to the user.
Thanks and regards,
User Rights In OLAP Cubes
Jul 12, 2000How 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?
Is It Possible To "extend" Role/user Rights Using SP...
Jun 8, 2001Hi 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
SQL Server Express - User Rights On PC?
Jan 18, 2007
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)"
Retrieving Rights Of An User On All The Tables In A Database
Aug 8, 2001Hi,
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.
Stored Procedure (DB User/login) Rights
Dec 29, 2004Does 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 RelatedLooking For A Script To Audit User Table Rights
Sep 28, 2004Hello 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.
SQL USER's ADMIN RIGHTS ON RESTORED DATABASE
Aug 22, 2007Thanks 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
User Rights And Executing Of SSIS Package
Mar 31, 2006I 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
How Can I Execute Stored Procedures With Correct User Rights?
Sep 3, 2007Hi,
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
Determine User Assigned Rights To Public Role
Oct 10, 2007Hi:
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.
SQL Security :: How To Grant User Rights To Edit Job Description
Aug 27, 2015What's best practice (security wise) in granting a user access right to edit say the description of a job.
View 2 Replies View RelatedMinimum Set Of Permissions And User Rights To Execute A Report Definition
Jan 28, 2008To 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.
Legacy DTS Package On SQL 2005 - Give User Rights To Save DTS Packages...
Oct 4, 2007We 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
Failure Setting Security Rights On User Account SQLServer2005BrowserUser${computerName}
May 29, 2007I'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?
Error Logon Failed - SSRS Service User Account Rights
Jun 12, 2006I 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
How To Grant Rights For The Anonymous IIS Web User IUSR_.. To Execute Scalar Function In Assembly
Jul 26, 2006Dear 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.
Assigning User Name And Password Form The Databse To Ssis Package For Data Flow Operations And Execute Sql Statement
May 8, 2008hi in my package, some sql operations need the special user name and admin privilage. so how do i create my ssis package so that when it executes it takes the given username and password from the table in some database.
View 8 Replies View RelatedI Need To Give DBA Full Admin Rights To SQL 2005 Without OS Windows Rights, Can Anyone Help Please!!
Jul 12, 2007The 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!!
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
Dec 7, 2006This 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
Assigning PK And FK
Feb 19, 2004Hi,
I have three tables -
Table A
Col1...Col2
1.....A
2....B
Table B
Col3...Col4
1.....X
2....Y
TableC
Col1...Col3....Col5...Coln
1........1...........H....Y.
A&B serve as the parent table for C. A&B are the dimension tables and C is the fact table. How do I assign the PK and FK in EM?
Thanks
Assigning A DB Value To A Variable
Mar 8, 2005Consider the following:
Sub regUser(s as Object, e as EventArgs)
If IsValid Then
Dim stuTable as String
Dim connStr as String
stuTable = "mytable"
connStr = "myConnectionInfo"
'check to see that student num is in the db
Dim connect as SqlConnection
Dim strSelect as String
Dim cmdSelect as SqlCommand
Dim strDbResult as SqlDataReader
connect = New SqlConnection(connStr)
strSelect = "SELECT stu_num, stu_fname, stu_lname FROM " + stuTable + " WHERE stu_num=@stuNum"
cmdSelect = New SqlCommand(strSelect,connect)
cmdSelect.Parameters.Add("@stuNum", txtStdNum.text)
connect.Open()
strDbResult = cmdSelect.ExecuteReader()
Dim stuFName as String
Dim stuLName as String
Dim stuEmail as String
Dim strRandom as String
Dim strPassword as String
Dim i as Integer
Dim charIndex as String
Dim stuMailMessage as MailMessage
Dim strHTMLBody as String
'declare stuFname, stuLname, stuEmail
stuFName = strDbResult("stu_fname")
stuLName = strDbResult("stu_lname")
stuEmail = txtStdEmail.text
'more code follows ....
In my DB I have a table with the columns stu_num, stu_fname and stu_lname. Each of these columns contains rows with data. However, the proceeding code gives me this error: Invalid attempt to read when no data is present (line 58 --> stuFName = strDbResult("stu_fname") ).
What am I doing wrong here? How do I assign the stu_fname in the DB to the page level variable stuFName?
As a little aside how do I say "If no record was found matching stuNum Then" ... ??
Sorry, I'm a .NET beginner ...
Assigning Value To A Variable
Feb 4, 2008Hi,
Is it possible to assign the column value to a user defined variable?