How To Pass The Privileges From One User To Another?

May 22, 2008



hi,
I created a View,like the following:
Create View viewSecure
as select * from sales.customer
go
Grant select on viewSecure to Andrew
go
Exec as login='Andrew'
Select * from viewSecure
go
Revert
go
Alter Authorization On viewSecure to Jerry
go
Exec as login='Jerry'
Select * from viewSecure
go
but, I received the error 'SELECT permission denied on object 'Customer', database 'AdventureWorks', schema 'Sales'.
Meanwhile, I re-granted Select Privilege to Andrew, I got the same error.
I know Alter Authorization command means to change the owner of an object. I got 3 questions need your help.
1. An user owned an object, if he may have no any privileges to this object?
2.Why I got the same error after re-granted Select privilege to Andrew?
3.How to pass privileges from one user to another?

Thanks a lot.

View 15 Replies


ADVERTISEMENT

DB Engine :: Extract All Privileges And User Password For A User In DDL Format For Server?

Jul 12, 2010

My goal is to write a DR plan where i am restoring all user databases onto a diffrent server in a event of hardware failure. I was trying to figure out a way to extract DDL of user accounts and their permissions on all user databases so i can simplify my DR documentation. 
 
This is the plan I came up with...to restore all system and user dbs on a different Physical SQLServer.

1. build named instance $PROD

2. restore master database

    - startup sqlserver in single user mode -m or DAC sqlcmd -S ServerName -U sa -P<xxx> –A
    net stop MSSQLSERVER$PROD
    net start MSSQLSERVER$PROD -m
    - restore database master from disk e:master.bak with replace;
   
3. start sqlserver normally

4. stop SQLServer agent

5. restore msdb

-restore database msdb disk e:msdb.bak with replace;

6. restart SQLServer

7. Restore User Databases.

8. Run Sp_change_users_login for all users

-Sp_Change_users_logins 'auto_fix','username'

View 7 Replies View Related

User Privileges Ms Sql

Feb 21, 2008

 Basically to defend against SQL injection I want to be able to stop basic users or admins from being able to drop tables or doing other damaging activities. I'm using ms sql express, how can I do this? A friend mentioned that he uses MySql and user privileges can be set up in this way.

View 2 Replies View Related

Grant All Privileges To A User

Aug 24, 2006

Is it possible to grant all privilege for all tables of a specified database through script? Because i have to send the script to user side and i can't do it manually in Enterprise Manager.regards,

View 1 Replies View Related

Retrieving User/Role Privileges - How ?

Jul 7, 2004

Hi,

I need to read and subsequently modify the privileges (rights) of a certain SQL Server user / role from within a Visual Basic Program.

Modifying seems to be easy using standard statements like GRANT/REVOKE. But what about reading all the rights a user has ?

I have researched SQL-DMO, but didn't find what I'm looking for.

Any idea ?

Mike

View 1 Replies View Related

How To Create A User With Minimum Privileges

Jul 19, 2015

I have a database for which I need the permissions to execute stored procedures, perform CRUD operations on tables, execute functions and SQL jobs. What should be the SQL command if I am to create a user for this database who will have the most minimum privileges to carry out these activities?

View 4 Replies View Related

Can A User Be Granted Read Only Privileges For Stored Procedures

Apr 26, 2004

Can a user be granted the ability to create stored procedures with read only capabilities? I wouldn't mind be able to UPDATE but simply need to read only.

ddave

View 13 Replies View Related

Pass User Name As A Parameter In A Query

May 13, 2007

just getting started with my first db driven web project...
I am using a MySql database with membership and roles and got that working fine. I have tables containing details of courses that users are enrolled on etc. and want to display a list of courses for the user that is signed in, so he can continue lessons on the one of his choice.
How do I pass the users name to the database query for a DataList control. So far I have tried lots of variations of the following:
<asp:SqlDataSource ID="dsCourses" runat="server" ConnectionString="<%$ ConnectionStrings:xxx %>"    ProviderName="<%$ ConnectionStrings:xxx.ProviderName %>"    SelectCommand="SELECT c.CourseName FROM courses c, enrolments e        WHERE c.CourseID=e.CourseID AND e.Username='<% =User.Identity.Name %>'"></asp:SqlDataSource>
<asp:DataList ID="DataList1" runat="server" DataSourceID="dsCourses">    <ItemTemplate>        <asp:HyperLink ID="HyperLink1" runat="server" NavigateUrl="Lesson.aspx">'<%# Eval("CourseName") %>'</asp:HyperLink><br />    </ItemTemplate></asp:DataList>&nbsp;</p>
However, the <% =User.Identity.Name %> doesn't work here (but it does elsewhere on the page) - not sure why?? The code works fine if I hard code a user name into the select statement.
Suggestions pleeeeeese!!
 

View 1 Replies View Related

How To Add New User Whit Pass For New Database

Jul 26, 2006

Hy,
I have question reagrding to SQL Server Authentication, I use SQL express and Microsoft SQL Server Management Studio Express. For creating and editing databases, I login whit Windows Authentication and it works fine, but as I need this new empty database for ASP script on my localhost ISS, I dont know what to enter for Username and Password as SQL Server Authentication.
Ex. here is this connection string where I need to enter U/P but what to enter if I use Windows Authentication?

strCon = "Provider=SQLOLEDB;Connection Timeout=90;Server=AMDSQLEXPRESS;User ID=???????;Password=???????;Database=test_database;"

View 8 Replies View Related

Need To Pass User Input Through To SQL Query

May 20, 2008

Hello Everybody,

I am sure this has been asked a thousand times before in a thousand different ways, but here goes...

I am in need of the ability to prompt users for a From Date and To Date to insert into a WHERE clause in SQL queries. What is the easiest least painful way of accomplishing this? A great many of our customers utilized SQL Server Express or MSDE, so they do not have the full gambit of SQL tools handy. Believe me when I say that I have searched high and low across the internet for this solution. I was hoping I might be able to accomplish this through VB Script or J Script. Or, anything reasonable.

Thanks in advance for your time and patience,

Rocky Mountain Tech

View 1 Replies View Related

Can We Pass Filed Name And Table Name As Parameter To User Defined Functions

Dec 29, 2005

Hi,
Following is the user defined function I want to get maximum value. It gives man an error "@strTableName must declare"


CREATE FUNCTION dbo.GetMaximum
(
@strFieldNamenvarchar(255),
@strTableName nvarchar(255)

)
RETURNS nvarchar(255)
AS
BEGIN

DECLARE @maxID int


SELECT @maxID=(SELECT IsNull(MAX(@strFieldName),0)+1 FROM @strTableName )

RETURN (@maxID)

END

View 5 Replies View Related

How To Pass Pass The Parameter In SQL Command In SSIS Package

Jul 31, 2006

Hi

   We already used Oracle Datasatage Server the following Query statement for Source and Lookup.here there is parameter maping in the SQl Statement . How can achive in SSIS the Folowing Querystatment?

 

Query 1: (source View Query)
SELECT
V_RDP_GOLD_PRICE.GDR_PRODUCT_ID, V_RDP_GOLD_PRICE.ASSET_TYPE, V_RDP_GOLD_PRICE.PREFERENCE_SEQ, V_RDP_GOLD_PRICE.RDP_PRICE_SOURCE, TO_CHAR(V_RDP_GOLD_PRICE.PRICE_DATE_TIME,'YYYY-MM-DD HH24:MI:SS'), TO_CHAR(V_RDP_GOLD_PRICE.REPORT_DATE,'YYYY-MM-DD HH24:MI:SS'), V_RDP_GOLD_PRICE.SOURCE_SYSTEM_ID
FROM
V_RDP_GOLD_PRICE V_RDP_GOLD_PRICE
WHERE
REPORT_DATE = (select max(report_date) from V_RDP_GOLD_PRICE where source_system_id = 'RM' )



 

Query 2: (look up )

 


SELECT
GDR_PRODUCT_ID,
TO_CHAR(MAX(PRICE_DATE_TIME),'YYYY-MM-DD HH24:MI:SS') ,
TO_CHAR(REPORT_DATE,'YYYY-MM-DD HH24:MI:SS')
FROM
V_RDP_GOLD_PRICE
where
GDR_PRODUCT_ID = :1 and
report_date = TO_DATE(:2,'YYYY-MM-DD HH24:MI:SS')  AND
PRICE_DATE_TIME BETWEEN TO_DATE(:2,'YYYY-MM-DD HH24:MI:SS') - 7) AND TO_DATE(:2,'YYYY-MM-DD HH24:MI:SS')
GROUP BY GDR_PRODUCT_ID, TO_CHAR(REPORT_DATE,'YYYY-MM-DD HH24:MI:SS')



 

please anyone give the sample control flow  and how to pass the parameter?

 

Thanks & regards

Jeyakumar.M

 

View 1 Replies View Related

Sa Privileges.

Nov 28, 2004

Hi,
in mixed mode,
is there a way to prevent access from user SA to a specific database?

thanks

View 1 Replies View Related

DBO Privileges

May 29, 2004

I have just noticed something very discomforting.

I was told that a user with DBO privileges is able to alter their own database. A conversation of course began to where I was in disagreement with him. The ultimate test of course would be setup the scenario. To my surpise he was right!

I checked the BOL documentation and my concerns were verified.

I have checked permissions on the user I created as well as on a user that previously exists on the MSSQL Server. Only DBO permissions were given to the tested users.

I thought maybe this had something to do with the autogrow setting which is a setting we would enable on a dedicated MSSQL Server but not on a shared MSSQL Server. I toggled this option and the DBO was still able to make size changes to their database.

This is very upsetting as we charge for additional reserved database space. Aside from that, we wouldn't want to have a user with unlimited resources to the server. I could easily fill up a hard drive if I were to update the autogrow setting of the database as DBO and run an infinite loop that would insert data into tables.

I then tested the ability for a user to restore a backup and to my surprise it worked without error for the DBO only privileged user. The DBO user was also able to restore previously dated databases assuming that they knew the file name which would not be hard to guess since it is appended with a date stamp (My_Database_20042905.BAK).

Why is this? Is there a way to correct this and prevent the DBO user to only have access to their database but not the above mentioned type privileges?

View 2 Replies View Related

Table Privileges

Jun 26, 2001

Is there a way to alias a table such that a particular user with privileges on that table (created by another user - not 'dbo') does not have to qualify it with the owner name? I am seeking a database level solution. Thanks.

View 1 Replies View Related

Privileges Problem

Sep 3, 1998

I`m having a privileges problem when I go into enterprise manager.
I am unable to do things like create an index. I believe every time I open enterprise manager it is logging
me in as a user other than sa. How can I change this setting so that when I open enterprise manager I`m loggged
in as sa?

View 2 Replies View Related

Execute Privileges On Sp

Jan 12, 2006

Hi. I'm trying to test something on a test db I have installed on my pc, but I am unable to process as I'm doing it. So, basically what I want is to give execute privilege on a procedure to a user, so the user can execute this procedure without having the privileges explicity granted on it (what this procedure do is to truncate a table on which the user has no access). As I've read, SQL Server stored procedures privileges runs with the definers permissions, not the one that is actually executing the procedure. So, what I'm doing is this: in query analyzer, logged in as sa, I did

use test

create table t ( a integer )

create procedure can_truncate as
truncate table t

sp_addlogin 'jmartinez',''

sp_grantdbaccess 'jmartinez','jmartinez'

grant execute on can_truncate to jmartinez

Then I went to connect again, as jmartinez and did:

exec can_truncate

and I get

Server: Msg 3704, Level 16, State 1, Procedure can_truncate, Line 2
User does not have permission to perform this operation on table 't'.

So, I wonder what more permissions would user jmartinez need in order to execute this procedure successfully. I hope you all understand what I am trying to achieve.

Thanks!

View 8 Replies View Related

Which Of The Two Or More Users' Privileges?

Feb 5, 2007

I am very new to the SQL database. I have the following query. I would appreciate if someone could clarify this for me:

I have created two users (user1 & user2) under the same login name test1 in SQL Server 2005 Database. Further I used the login name (test1) & password (******) of SQL Server in connection string to connect to database.

Now I want to know that how & where can I refer the user name (user1 or user2) to use its previliges.

How will I know that which user's privileges level is used in the connection.?

View 1 Replies View Related

Privileges To Run Trace

Oct 1, 2007

Is there any way I can give a user read only access to the database, yet have privilges to run a trace.

Any suggestions and inputs would help

Thanks

View 1 Replies View Related

DTSRUN Privileges?

May 4, 2006

I am trying to get a DTS package to be run from the command line withthe dtsrun utility. The DTS package is stored in the database. The userI supply is a user in the database. I get an error stating "SQL Serverdoes not exist or access denied." It looks to me like the SQL Serverinstance does exist because it tries to start the package. I get"DTSRun: Executing". If I put in a server that is non-existent, I do notget that message. I also know that my username and password are correct.Here is output from my attempt to run dtsrun for my DTS pkg (server,user, password change to protect my db security):C:>dtsrun /Sserver_name /Uuser /Ppass /Npkg_nameDTSRun: Loading...DTSRun: Executing...DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1DTSRun OnError: DTSStep_DTSExecuteSQLTask_1, Error = -2147467259 (80004005)Error string: [DBNETLIB][ConnectionOpen (Connect()).]SQL Serverdoes not exist or access denied.Error source: Microsoft OLE DB Provider for SQL ServerHelp file:Help context: 0Error Detail Records:Error: -2147467259 (80004005); Provider Error: 17 (11)Error string: [DBNETLIB][ConnectionOpen (Connect()).]SQL Serverdoes not exist or access denied.Error source: Microsoft OLE DB Provider for SQL ServerHelp file:Help context: 0DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1DTSRun: Package execution complete.I suspect that my user I am connecting to the database with does nothave privileges to execute the DTS package. I cannot determine, fromBOL, what privs I need to grant to this user to let them execute thispackage. Any ideas?TIA,Brian--================================================== =================Brian PeaslandJoin Bytes!http://www.peasland.netRemove the "nospam." from the email address to email me."I can give it to you cheap, quick, and good.Now pick two out of the three" - Unknown

View 5 Replies View Related

Issues With Privileges

Aug 11, 2006

I am having trouble with providing the minimum security to a user. After issuing the following:

GRANT EXECUTE ON SCHEMA :: DBO TO skillsnetuser;

I test the permissions with

exec as login = 'skillsnetuser'

exec prcElmtList 1, 1, 102268

revert;

and receive this message

Msg 229, Level 14, State 5, Line 2

SELECT permission denied on object 'Org', database 'SNAccess_Dev', schema 'dbo'.

The principal that owns the dbo schema is dbo and is the principle for all procedures and tables in that schema.

What can I do to shed some light on what is causing this access problem?

View 10 Replies View Related

Insufficient Privileges

May 25, 2008

After installing Express, I tried running the QuickStart utility and received an error that I have insufficient privileges to create. I am the administrative user on my laptop and don't understand why I am unable to run the utility.

I did have an instance previously and had no problems with it until it was corrupted somehow.

I am running on Vista.

Any help will be greatly appreciated.

View 12 Replies View Related

Script To Know Users And Their Privileges

Apr 10, 2015

Is there any script to know users and their database roles privileges and server roles of particular database?

View 1 Replies View Related

Granting Privileges With A Script

Sep 14, 2005

Hi,

I'm trying to write a script to grant privileges to a user (we are trying to allow Windows Authentication in our application).

Previously, we used the following syntax:

grant select, insert, delete, update on area to mattuser

Where mattuser is a valid sql server user.

However, we want to do something like the following:

grant select, insert, delete, update on area to MATT2000IUSR_MATT2000

Where IUSR_MATT2000 is a valid user on computer MATT2000.

We get an error when we try to run this script as follows:

Incorrect syntax near ''

How do we grant permissions for this user, other than doing it manually?

Regards.

Matt.

View 3 Replies View Related

Grant/Revoke Column Privileges

Oct 20, 1999

How can I set / view column privileges.

I want to remove the select privilege from a salary column to a certain group of users.

Thomas

View 4 Replies View Related

SQL Server 7.0 And Setting Column Privileges

Oct 14, 1999

How do you set column privileges in the SQL Server 7.0’s Enterprise Manager. It was so easy in 6.5’s but now it seems that the only way to do it is through the stored procedure.

View 1 Replies View Related

SQL Server Agent - Account Privileges.

Aug 28, 2007

Hi all,

Please let me know what specific privileges an user account needs to be used as LOG ON AS account for SQL Server Agent in SQL Server 2005.

Does the account needs to me in the domain administrator group?

Thanks,

Hariarul

View 2 Replies View Related

Inserting Records With Limited Privileges

Jul 23, 2005

I am trying to insert records via ASP, with a user that has only writeaccess to the table (db_datawriter, db_denydatareader).That way, if the server is ever compromised, the access informationstored in the source code's connection string will not allow anybody toactually read the database.The problem is that I would like to use ADO methods to insert the data(to prevent SQL injections), but I can't seem to get the rightconnection. It works in plain SQL, but I'd rather not use it.My current code looks like this:connection="Provider=SQLOLEDB.1;User ID=DBwriter;Password=XXX;DataSource=MYSERVER;Initial Catalog=MYDB;"set conn=server.createobject("ADODB.Connection")conn.mode=2 ' adModeWriteconn.open connectionSet rs = Server.CreateObject ("ADODB.Recordset")rs.Open "MYTABLE", conn, adOpenKeySet, adLockPessimistic, adCmdTablers.AddNewrs.Fields("testfield") = "TESTDATA"rs.UpdateAnd the error I get is:Microsoft OLE DB Provider for SQL Server (0x80040E09)SELECT permission denied on object 'MYTABLE', database 'MYDB', owner'dbo'.(If I use a User with read privileges in the connection stringeverything works fine.)

View 3 Replies View Related

SQL 7.0 Stored Procedure Execute Privileges Only?

Apr 15, 2006

SQL 7.0 Stored Procedure Execute Privileges Only?Does anyone know if a user/pass can be set up to givea sql 7.0 user stored procedure execute privileges only.That way, even if they get into the database, they cando nothing except run the stored procedures.Thanks,Ed

View 3 Replies View Related

SQLServer And SQL ServerAgent Account Privileges

Jun 15, 2007

After installing SQL Server 2005, a security scan was performed on the SQL server. Below are a few items that the scan identified within the Windows User Rights Assignment as potential vulnerabilities; it is worthy to note in Microsoft's defense that we lock things down pretty tightly in our IT shop. I suspect the SQL Server install assigns these OS privileges to the SQLServer and SQLServerAgent accounts by default. I have not heard of the 1st, 3rd and 4th below and suspect that they are not essential to the normal operation of SQL Server, but would like to know if anyone out there knows for sure. We are considering eliminating some or all of these privieleges for the SQLServer and SQLServerAgent system accounts at the OS level.



1) SQLServer and SQLServerAgent accounts have "Bypass Traverse Setting" privilege within Windows User Rights Assignment

2) SQLServer and SQLServerAgent accounts have "Log on as Batch Job" privilege within Windows User Rights Assignment. I realize I need this to schedule SQL Server jobs which run batch jobs and such, but any other reason to keep this privilege.

3) SQLServer and SQLServerAgent accounts have "Memory Quota" privilege within Windows User Rights Assignment

4) SQLServer and SQLServerAgent accounts have "Replace Process Token" privilege within Windows User Rights Assignment



Any guidance on this would be greatly appreciated.



Regards,



Jason

View 1 Replies View Related

Run SP From Remote Computer Needs Db_owner Privileges ?

Oct 13, 2006

Hi All,

I created SP and enabled db_datareader and db_datawriter for roles for BUILTINUsers on database level.

I can call that SP from my application if I am accessing DB from my app running on the same machine. If I run app on other machine SP throw exception related to lack of permission.

I fixed that by enabling db_owner roles for BUILTINUsers. But it is not good fix from security point of view.

My question is: is there any other way to allow regular user to run that SP by not giving him db_owner privileges?

Also I have to mention that my SP procedure has some dynamic SQL code.

I would greatly appreciate any help,

Thanks,
Roman

View 1 Replies View Related

SQLServer And SQLServerAgent Account Privileges

Jun 13, 2007

I recently installed SQL Server 2005 and setup a database for one of the systems that I support as a DBA. After installation and the system, which has remote developers, was tested successfully, our security group performed a security scan on the SQL server. The scan revealed a few potential vulnerabilities. Below are the questionable items that the scan identified within the Windows User Rights Assignment. I believe the SQL Server installation assigns these system privileges to the SQLServer and SQLServerAgent accounts by default. I'd like to know how many, if any of these privileges, are necessary.



1) SQLServer and SQLServerAgent accounts have "Bypass Traverse Setting" privilege within Windows User Rights Assignment

2) SQLServer and SQLServerAgent accounts have "Log on as Batch Job" privilege within Windows User Rights Assignment. I realize I need this to schedule SQL Server jobs which run batch jobs and such, but any other reason to keep this privilege.

3) SQLServer and SQLServerAgent accounts have "Memory Quota" privilege within Windows User Rights Assignment

4) SQLServer and SQLServerAgent accounts have "Replace Process Token" privilege within Windows User Rights Assignment



Any guidance on this would be greatly appreciated.



Thanks in advance,



Jason Malasovich

SQL Server DBA

View 1 Replies View Related

Querying ALTER TRACE Privileges

Jul 20, 2007

Is there a query that will determine the presence or absence of the ALTER TRACE privilege for the current user?



Also, a note to development. Why is it necessary to have ALTER trace permission to get a read only record set of a) what traces are running and B) the event history of each trace. To ALTER is to change, to SELECT is to view! A separate SELECT TRACE permission would be both useful and intuitive.



Randy Hersom

Habilitation Software Inc.

View 4 Replies View Related







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