Why A DBA Needs Sa Access Rights

Jul 18, 2002

I remember seeing a document on this site a couple of years ago that explained reasons why a DBA needs sa access rights. I can go into BOL and generate a list of things you can only do with sa rights. However the article I am looking for was well written, much better than I could do.

My infrastructure team has decided that the DBA's and Sr. Developers will not have sa access rights. All schema changes, stored proc creation, view creation, database backups, maintenance plans, etc will go through their server engineers. They do not understand what they are getting into.

Does anyone have a nice document that would aid me in my efforts to convince the Infrastructure group to change their "new" policy?

Thanks for any info!!

Jeff

View 3 Replies


ADVERTISEMENT

Access Rights

Oct 19, 2000

hi, I am having a database in sql server 7.0. it has a web front end database. how can I grant access to the tables. do I create a guest logins in the security folder, then in the database user tab, I give access as read,write. Or there is another way to do it.

Thanks
Ali

View 2 Replies View Related

Access Rights...

Apr 19, 2006

Hello,




I want to restrict the database not to be accessed from anywhere except
my webservice...I mean, my client applications or anyone else can not
be able to access the database...





How can I do this?



Thanks very much...

View 1 Replies View Related

SQL Server Access Rights

Aug 4, 2004

I am having trouble openning a connection to a sql server database that resides on another machine. When the web server and SQL server run on the same machine, everything works fine. When the web server and sql server are located on different machines, I get an access rights error when i try to open the connection. I suspect that this involves trust levels, but all the tweaking I have done has not resolved this issue. Any help would be much appreciated!

View 1 Replies View Related

Time Based Access Rights...

Jul 18, 2001

Hi All,

Is there a way in SQL Server 7.0 or 2000, where I can grant/deny/revoke access rights on a database objects like Table, Stored Procedures for a particular time of the day.

Example: I want to prevent user A from acessing Table x and Stored Procedure Y from 9Am to 12 noon everyday. After 12 Noon till 8.59 AM he can have access to Table x and Stored Procedure Y.

Is there a way to do this at SQL Server level.

Thanks
Sri

View 1 Replies View Related

Maintainence Plan Access Rights

Oct 30, 2007

Guys,

What are roles and access rights I need to assign my backup operator so that he can see Maintainence plans under Management node of SQL Server Management Studio. I do not want to assign any admin related privileges.

Thanks

View 3 Replies View Related

Rights For Clickonce Report Builder Access?

Feb 7, 2007

I'm attempting to grant rights to Report Builder as deployed as part of my TFS install. My problem is that I have to add my users to the builtinadmin group in order for them to see the report builder button on the SQL Server Reporting homepage & then have rights to launch the ReportBuilder click once app. I obviously do not want make users admins on the box, but I've tried adding them to all of the other groups having to do with SQLServer to no avail. How can I grant users access to launch the Report Builder app?

View 6 Replies View Related

Access Rights To Two Mssql Dbs Via Password Protected Role

Jan 30, 2007

I 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.

View 3 Replies View Related

SQL 2012 :: Access Rights To Execute Stored Procedure If There Is No DML Involved

Jul 2, 2014

I would like to provide the db_datareader and db_executor role to a particular SQL Server Login in a database
But, I would like to avoid any INSERT's, UPDATE's or DELETE's that may happen by calling the stored procedures

I tried assigning the db_denydatawriter role but it doesn't seem to be doing the trick as the INSERT's, UPDATE's and DELETE's were still working

Is there any way to provide the db_datareader and db_executor role but avoid any DML actions.

View 7 Replies View Related

I Need To Give DBA Full Admin Rights To SQL 2005 Without OS Windows Rights, Can Anyone Help Please!!

Jul 12, 2007

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!!

View 8 Replies View Related

Help With Rights

May 3, 2001

I want to give user right to create temp tables but I don't want to allow him modify any other tables, or any other rights. Any ideas?

View 1 Replies View Related

Sa Rights

Jul 8, 1999

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?

Any help is much appreciated.

Thanks.

View 1 Replies View Related

DBO Rights

Nov 22, 2000

Hi

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.

Thanks

View 1 Replies View Related

SA Rights

Jan 29, 2004

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.

Any suggestions are greatly appreciated.

Thanks,

Isaac

View 2 Replies View Related

Rights

Jul 20, 2005

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.

View 1 Replies View Related

DBA Role And His Rights

Oct 30, 2001

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

View 3 Replies View Related

DBO Aliased And SA Rights

Aug 9, 2000

I have a user that is requesting sa rights on a test server. I prefer to give him aliased
dbo rights. What is the difference between the two?? What can he not do with dbo
that he could with sa??

View 1 Replies View Related

Rights On SP/Urgent

Feb 20, 2001

Hi,
I have public and dbo rights on a sp.
I am trying to call this sp thru a EntityBean(Java).
But I am getting an error.
Can anyone tell me what all rights do I need to execute this stored proc.?
TIA.
Jay

View 1 Replies View Related

User Rights

Nov 28, 2005

Hi 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.

View 4 Replies View Related

Users And Their Rights

Nov 20, 2006

Hi All,

I've restored the dev db from the prod backup which overwrote the users and their rights in dev db. Is there any way that I can find out what those rights were? I have the list of users in dev db but not their rights . Thanks.

View 1 Replies View Related

Question About DBA Rights

Apr 11, 2002

Here is a debated question:

Does the DBA need admin rights?

They are not responsible for the server, user accounts, software updates of any kind or odbc configurations. This is controlled by the LANWAN server support group.

With the assigned SYSADMIN role, service account as local administrator, they have NTFS permissions the the DATAApplication partition and rights to stop and start all related services to SQL.

They can also access event viewer, performance monitor and other MMC snap-ins as read only.

Can they do required functions?

Thanks,

RJ

View 1 Replies View Related

DBA Role And His Rights

Oct 24, 2001

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

View 1 Replies View Related

Rights Issue

Jan 14, 2005

How do I text base add my domain users group with full rights to my database. I am using teratrax to manage my database. This is what comes up when I click on new database user

-- Replace all lower case words with your own code.

EXECUTE SP_GRANTDBACCESS 'login', 'user_name'

View 3 Replies View Related

SQL Agent Rights

Jul 5, 2007

If I connect with SQL Management Studio to a server I cannot open or change SQL Agent jobs. (I can see them, but if I ask properties it opens a new job window).

At home I have no problem managing jobs.

I also cannot stop or start the SQL Agent.

What rights do I need? I am not in a domain, but with using the same username and password on my laptop as on the server and I have no problem connecting, add/changes databases and such.



Henri
~~~~
There's no place like 127.0.0.1

View 6 Replies View Related

Sp_elpuser Rights

Jul 23, 2005

what users have the rights to execute sp_helpuser?

View 1 Replies View Related

Db_owner Rights

Jul 23, 2005

If I on a remote hosting server have db_owner rights, do I then also havedb_securityadmin and db_dlladmin rights?BRGS, TCHillII

View 1 Replies View Related

Redistribute Rights For Me?

Mar 6, 2008

Hi all,

I'm just a hobby programmer that writes programs for my personal use and maybe for friends.

I'm planning on using VB.Net 2008 Express edition for my development. I was using SQLite before with VB.Net 2005, but it seems the ADO.NET provider will not work with the VB.Net 2008 Express edition, so I'm thinking of moving to SSCE.

Do I still have to sign up for redistribution rights? I looked at the choices (What Best Describes You) when you start to sign up for those rights and I don't think I fall under any.

I was hoping to either just copy the 7 DLLs to the client computer or use the redistributable MSI installer to install SSCE.

Any help will be appreciated.

JB

View 3 Replies View Related

User Rights

May 17, 2007

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

View 1 Replies View Related

SRS Reporting Rights

Oct 18, 2007



I have recently published a report to SRS. I created a new service account and assigned that account the "Browser" role for the report that I wanted the service account to access. However, upon connecting to the SRS URL with the new service account, I am unable to view any reports. I then added the "view" and even the "Content Manager" role, and I still could not see the reports. I made the service account an admin on the box and I could see the necessary reports. Is there a local group that I need to place user accounts that I want to be able to access the reports I publish to the srs url?

Thanks,

Joe

View 1 Replies View Related

User Rights

Dec 7, 2006

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...

View 4 Replies View Related

SQL Restore Rights

Nov 9, 2007

Hi All,
I've got an SQL 2005 server setup with some databases. I'm trying to set it up so that a user can upload his database backup and then restore the DB using Studio Express but am having some issues with it. The user can upload his database fine, but when we try and go in to restore it, he can't view the directory or file of the backup. I've added the service account that SQL runs as, as well as the SQL2005%Machine%etc... user without much luck. The only thing I can think of from here is SQL permissions and I'm a little vague on how to accomplish what I want. One other thing is that if I add sysadmin rights to the user, they can see the directories fine. I tried adding dbcreator as recommended by other posts and that didn't work either. Any help would be greatly appreciated. Thanks!

Brad

View 5 Replies View Related

Administrator Rights

Aug 8, 2007


View 1 Replies View Related

Question About Rights

Jan 15, 2008



We are conducting a pilot around the 2007 DM ad in for Excel for use at our company. We have sql server enterprise (64 bit) installed with Analysis server (all on one box). I have set up the Data Mining Ad in db on the Analysis Server. I've given a business user admin rights in the data mining db on AS. We are trying to use a table in a database on the same server as input to an estimate model. When I create the model (I'm a sysadmin), I have no issues using excel to point to the table and run the model. When the user runs the model, he get the following error.


Errors in the high-level relational engine. A connection could not be made to the data source specified in the query.
Either the xxx user does not have permission to access the " object, or the object does not exist.

The user does have access to the table - he actually created it. Since this works when I run the model, it seems like this has to be how AS is passing in the table? Has anyone seen a similar error?

On a different topic, how is it best to configure user access to datamining functionality? Create one AS db per user? Thanks!

Dave

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved