A Query To Determine A Role Or Id's Access To An Object... Do-able?

Oct 19, 2007

Hey there.

I must write a stored procedure to update a table and want to make sure the ID used in both Acceptance and Production will have the necessary access.  The ID will inherit it's access to the resource though a ROLE.

Wondering if there are queries out there that can traverse for example, the roles/id's that have access to it... Thx!

View 2 Replies


ADVERTISEMENT

How To: Determine If Current Windows User Has Login Access, Database Access And If They Are A Member Of A Specific DB Role.

Mar 25, 2008


I need to determine the following about the current authenticated Windows domain user who is trying to access a SQL Server via a trusted connection.

1 Has the current user been granted login access to the trusted SQL Server?

2 Has the current user been granted access to a specific database?

3 Is the current user a member of a specific database role such as (DB_ROLE_ADMINISTRATORS)?

Thanks,
Sean

View 6 Replies View Related

Table Query Fails With Object Not Found Error After Assining Sysadmin Server Role To Login

May 14, 2015

I have dw schema in the database, owned by user dw.The login name is dw. The login had db_owner right in the database. The default schema for the login on the database is dw.Now Once I assign 'sysadmin' serverrole to dw login, I started seeing stored proc not found error, if try to execute stored proc without mentioning dw.spname...Also I am seeing table not found error while quering tables under dw schema, after the change.

View 5 Replies View Related

Query To Determine Public Access

Aug 30, 2007

Is there any T-SQL Script or SP that would determine what tables public users have access to? I only ask because I would like to make sure that any users with public rights do not have access to SYSXLOGINS, SYSDATABASES, SYSOBJECTS, SYSCOLUMNS and mswebtasks tables. Thanks
-Kyle

View 2 Replies View Related

Determine A Role

Jun 18, 2007

hi,



since I am kind o'new with SQL, I preffer get an advice fro you pro's: I created an application which performs access to a database on an SQL server. the application will be used by a few different users, each on a different computer. the application calls stored procedures, updatesinserts records in tables on the SQL and delete rows. what would be the best role to define the users activity ? How do I limit their activity ONLY to the specified actions ?

View 1 Replies View Related

How To Determine Mirroring Role?

Dec 18, 2006

Once I have identitied that mirroring is enabled on database via SMO Database.IsMirroringEnabled, I need to determine the mirroring role. I noticed an enumerated type - MirroringRole, but no SMO method/property to access it.

I have tried to query the sys.database_mirroring table directly, but this fails with an exception on the mirror database - as it is being mirrorred :-(

What is the recommended way to determine the mirroring role?

Thanks, Nick

View 5 Replies View Related

Determine User Assigned Rights To Public Role

Oct 10, 2007

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.

View 1 Replies View Related

How To Show Role Securables Of All Object

May 7, 2008

how to show on t-sql role securables of all object like in: database role propertipes -> securables -> explit permissions of
pls help

View 1 Replies View Related

Problem In Converting MS Access OLE Object[Image] Column To BLOB (binary Large Object Bitmap)

May 27, 2008

Hi All,
i have a table in MS Access with CandidateId and Image column. Image column is in OLE object  format. i need to move this to SQL server 2005 with CandidateId column with integer and candidate Image column to Image datatype.
its very udgent, i need any tool to move this to SQL server 2005 or i need a code to move this table from MS Access to SQL server 2005 in C#.
please do the needfull ASAP. waiting for your reply
with regards
 
 
 

View 1 Replies View Related

Determine Last Access Of A Database Or Table

Dec 18, 2007

Does anybody know of a way to determine the last date/time a table has been accessed (query/update)? I've done enough research to know that this isn't easy. However, perhaps somebody has figured out a way through some of the stats that SQL Server keeps to determine the last access of a table. I have recently been put on a team that had no DBA and has a number of databases out there. They would like to determine which databases are inactive and get rid of them. I am a developer and haven't had much SQL Server Administration experience. Any info will help greatly! Thanks!   

View 2 Replies View Related

Predict Query Gives 'DMPluginWrapper; Object Reference Not Set To An Instance Of An Object' Error

Mar 17, 2008



Hi,

I am trying to develop a custom algorithm. I have implemented and tested training methods, however I fail at prediction phase. When I try to run a prediction query against a model created with my algorithm I get:


Executing the query ...
Obtained object of type: Microsoft.AnalysisServices.AdomdClient.AdomdDataReader
COM error: COM error: DMPluginWrapper; Object reference not set to an instance of an object..
Execution complete


I know this is not very descriptive, but I have seen that algorith doesn't even executes my Predict(..) function (I can test this by logging to a text file)
So the problem is this, when I run prediction query DMPluginWrapper gives exception -I think- even before calling my custom method.

As I said it is not a very descriptive message but I hope I have hit a general issue.

Thanks...

View 3 Replies View Related

DB Engine :: Determine Database Access History?

Jul 31, 2015

I was asked to determine the last time 2 databases were accessed.

Are the .trc files an accurate way to determine the database access history?

View 5 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

Guest Principal Assigned To Datareader Role Does Have Access

Mar 1, 2007

Hello (help),

In SQL2000, when the Guest account was assigned into a role, such as db_datareader, then querying across databases worked just fine.

Specifically:
I have a Report Writer application that connects to the SQL Server with a login (ReportRunner) that actually has very limited permissions on a database.
The connection is then set (sp_setapprole) to use an Application role (App_RR) that has the necessary permissions.
The report-writer app calls a Stored Procedure that gathers data from several other databases (on the same SQL instance).
In SQL 2000, accessing these other databases was done through Guest - we assigned Guest to the db_datareader role. All worked fine.

We've just upgraded to SQL2005: reports started failing. It seems that although guest is assigned to the db_datareader role, the permissions for Guest don't allow selecting from tables via the db_datareader role: we've had to GRANT SELECT TO Guest specifically on the tables necessary for the report.

Is anyone aware of a design change withing SQL Server such that the Guest principal's roles are disregarded when assessing permission? Is there a new and better way to structure the permissions?

Thanks in advance for your help.
Mark Starr

View 9 Replies View Related

Setup And Upgrade :: Users With Sysadmin Role Can't Access Network Drives

Oct 12, 2015

When I create logins for SQL Server 2012, something strange happens. When I assign the sysadmin role to a login, the login loses the access to the network drives (for example when creating or attaching databases). The only possible place is the c:drive of the computer that is running SQL Server.

View 11 Replies View Related

Determine Fastest Query In Query Analyzer

Sep 17, 2005

I am trying to determine which of three stored procedure designs arefastest in the Query Analyzer:One query is a straight SELECT query with all desired rows and a dozen(tblName.RowName = @param or @param = Null) filters in the WHEREstatement.One query populates a #Temp table with the UniqueIDs from the resultsof the SELECT query in the above example, then joins that #Temp tableto get the desired rows.One query users EXEC sp_executesql @sql, @paramlist, @paramin which the @param has the dozen filters.What I'm trying to determine is which is the fastest.Each time I run the query in Query Analyzer it returns the samerecordset (duh!) but with much different Time Statistics.Are the Time Statisticts THE HOLY QRAIL as far as determining which isfastest, and what so I want to look at, the Vale or the Average? Inotice there are different numbers of bytse sen and bytes received foreach of the three queries.Any illumination on this is appreciated.lq

View 4 Replies View Related

SQL 2012 :: Give User / Domain Group Only View Access On Agent Role?

May 20, 2014

I have been struggling with this one for awhile now.I have a domain group which only must view the steps and history of all agent jobs.I have added the group to the sqlagentreadergroup.I have created a new role and denied this role,add job,update job,delete job etc execute permissions.But the user still can change ,delete or create a new job.

All the groups and users in th new role,does not have sysadmin rights.

we have sql 2012 enterprise version

What else can i try.I need this for audit purposes.

View 7 Replies View Related

Determine Query Time

Feb 12, 2008



Hi, I'm sorry if this is the wrong place to ask this question. These forums are overwhelming and I couldn't find the appropriate place to post.

I am wondering if there is some way to determine when a query has completed. What I want to do is use a PleaseWaitButton in asp.net when the user clicks to run a query and have the button reactivate after the query has completed. I know how to set a specific wait time but I do not want the user to have to wait a default time for the query to complete. Any help you may provide would be greatly appreciated.

Thanks!

View 2 Replies View Related

Query To Determine Backups

Mar 6, 2008

I need a query that gets all of the databases on a server and determines if a backup has been taken. I would normally use the jobs query I currently have, but it wouldn't tell me when the last backup was taken for a new database. I think I should be able to use sp_helpdb or some table from the msdb database. Also, this has to work on SQL Server 2000/2005. If anyone has anything I'd be very grateful. Thanks in advance.
-Kyle

View 2 Replies View Related

Query To Determine Table Size

Mar 7, 2001

Hi all,
I am looking for a query to determine the exact size of a large table.

Thanks
JJ

View 1 Replies View Related

Using An Array To Determine Query Order??

Oct 17, 2006

Hi.

I have a VB.NET function that returns an array of Integers.
Say, FunArray = [2, 3, 5, 8, 6, 23, 1, 10, 20 , 4, 54]
One characteristic of the array is that no two numbers
repeat - it reflects the IDs of my Users table. And, that
it is not ascending or descending.

What I would like to know is how do I sort my query in
the order of the integers in this array? Ideally, I
would like to use ORDER BY for this query.

thanks in advance.

View 2 Replies View Related

MSSQL 2005 Inserting Data On Tables Created By Regular Users With Ddl_admin Role, Using Access Or Other Front End Apps

Feb 14, 2008

Writing to tables created by regular users on MSSQL2005

I have users creating tables through an application, I gave them ddl_admin, datareader, datawriter. They can create tables but cannot insert/update data (to their own tables), I cannot insert data either using Access or any other application to those tables created by them (under dbo schema) Is there something I am missing with permissions? Thank you very much

View 3 Replies View Related

Database Role/User Query

Sep 21, 2006

Anyone have a tsql query that will give me a listing of database roles and their users already put together?

View 1 Replies View Related

Auditing Access To Object

Dec 21, 2006

Hi,
I need to audit any access to table (example table creditcards)
profiler work fine if table creditcards referenced
by select/ upadate/delete or procedure



but if I create view

create v_alex
as
select * from creditcards

profiler with all events would show only v_alex

Application modifing and number of views growing ...

Any way to trace accees to creditcards if it referenced by view ?

View 5 Replies View Related

Monitor Object Access

Apr 26, 2007

I would like to remove an index from my database, but I need to verifythat it is not being accessed before I do that.I tried running Profiler, but it doesn't seem to be giving me theinformation I need. I put in a filter for ObjectName, but it seems tobe ignoring it - i get several entries in the profile trace, but novalue in the ObjectName column.Is there an easy way to monitor for object access?Thanks so much!SQL Servef 2000 SP4(I also have 2005, SP2, if there's a better way to do this with 2005)

View 3 Replies View Related

Transact SQL :: Query To Determine Overlapping Date Ranges (by Category)

May 12, 2015

Given the data below, I have a couple needs:

1) Query to determine if any date ranges overlap (regardless of category, e.g., row ids 6 & 7 below)

2) Query to determine if any date ranges of the same category overlap

declare @t1 table (id int primary key, category int, start_date datetime, end_date datetime)
insert @t1 select 1, 1, '1/1/2015 12:00:00 AM', '1/15/2015 12:59:59 PM'
insert @t1 select 2, 1, '1/16/2015 12:00:00 AM', '1/31/2015 12:59:59 PM'
insert @t1 select 3, 1, '2/1/2015 12:00:00 AM', '2/15/2015 12:59:59 PM'
insert @t1 select 4, 1, '2/16/2015 12:00:00 AM', '2/28/2015 12:59:59 PM'
insert @t1 select 5, 1, '3/1/2015 12:00:00 AM', '3/15/2015 12:59:59 PM'

[code]....

View 7 Replies View Related

Sproc To Determine If Query Will Produce Invalid SqlNotificationEventArgs Event

Feb 7, 2007

I'm using an SqlDependency so I'll get an alert when the data under a query changes. When the query does not meet the criteria spelled out here http://msdn2.microsoft.com/en-us/library/aewzkxxh.aspx then I immediately get a SqlNotificationEventArgs event with the "Invalid" note. This is good.

I would like to know that a query is invalid before I try it with my ASP.NET application. Is there a sproc I can give to my SQL developers so they can test that they haven't used any stuff that would invalidate SqlDependency? What I'm hoping is that there's already a system sproc which could be called...

exec sp_QueryInvalidForNotification "exec myTestSproc 99"

Does such a sproc exist? Or does anyone know how to write one?

View 1 Replies View Related

Sql 2005 Database Role Vs Application Role ?

May 18, 2007

Ive been reading over the documentation and some stuff online, but I still dont really understand what the difference is and when you would use one vs the other. Can someone put it in simple terms for this dummy (me) ?

thanks

View 2 Replies View Related

How To Determine, Inside A Function, If A Linked-server-query Returned Results

Feb 13, 2004

Hi, have configured an ODBC linked server for an Adaptive Server Anywhere (ASA6.0) database.
I have to write a function (not a procedure) that receives a number (@Code) and returns 1 if it was found on a table in the linked server, or 0 if not. Looks very simple...
One problem, is that the queries on a linked-server must be made through the OPENQUERY statement, which doesen't support dynamic parameters. I've solved this making the whole query a string, and executing it, something like this:

SET @SQL='SELECT * FROM OPENQUERY(CAT_ASA, ''SELECT code FROM countries WHERE code=' + @Code + ''')'
EXEC sp_executesql @SQL

(CAT_ASA is the linked-server's name)

Then, i would use @@ROWCOUNT to determine if the code exists or not. But before this, a problem appears: sp_executesql is not allowed within a function (only extended procedures are allowed).
Does somebody know how to make what i want?? I prefer to avoid using temporary tables.
Thanks!

View 3 Replies View Related

Application Role How Query A View Whose Reference Table Is In Another Db?

Aug 23, 2007

Hi everybody.
I created an application role in a database (DB1) and gave it all the rights on a view in DB1 which refers to a table located in another db (DB2). I also gave the rights to the app role on a table of DB1
I tried to use this app. role through the sp_setapprole launched by a user (server principal?) which is SQL Server administrator (and local administrator (Win 2003 Server)).
With the following query
SELECT USER_NAME()

I see that the approle is being used.
Than, if I query the table on DB1 everything works, but if I query the view, referring a table in db2 I get following error:


The server principal "NameOfServerPrincipal" is not able to access the database "DB2" under the current security context.
What should I do to make it work?

The table in DB2 has the same schema of the view in DB1 which refers to it.
I put the DB1 TrustWorthy and both the database have the db_chaining option activated.

Any idea on how to solve the problem would be widely appreciated.
Thank you very much.
Vania

View 5 Replies View Related

Invalid Object Name When Linking To SQL Table From Access!

Nov 17, 2006

After Upsizing a table to sql I linked to that table using access db

Now when i use one of my forms i get a [Invalid Object name "tablename''], not sure why but i am clearly link and the table is in sql!

Can you help!

View 1 Replies View Related

Access To TableAdapter Object In WebPage Class.

Feb 2, 2007

Hi, I'm using Reporting Services on my website and for some reports, i get the timeout error after 30 secs.

I declared my object using the Reporting tools and I am using ObjectDataSource.

My report is declared in my aspx page:

<rsweb:reportviewer id="ReportViewer1" runat="server" font-names="Verdana" font-size="8pt" Width="100%" Height="600px">

My ObjectDataSource is declared in my aspx page under my report tag:

<asp:ObjectDataSource ID="ObjectDataSource" runat="server" OldValuesParameterFormatString="original_{0}" SelectMethod="GetData" TypeName="ReportsTableAdapters.ViewingTotalsTableAdapter">

My TableAdapter is declare in my Reports.xsd file:

<TableAdapter BaseClass="System.ComponentModel.Component" DataAccessorModifier="AutoLayout, AnsiClass, Class, Public" DataAccessorName="ViewingTotalsTableAdapter" GeneratorDataComponentClassName="ViewingTotalsTableAdapter" Name="ViewingTotals" UserDataComponentName="ViewingTotalsTableAdapter">

I am trying to access the TableAdapter object from my WebPage Class on the Page_Load event , so I can modify the Command.CommandTimeout. Unfortunately, I cannot find a way to have access to the TableAdater object.

Anyone has any suggestion ?

Thanks,

Richard



View 1 Replies View Related

Trouble With Cursor Designed To Revoke Object Access.

May 30, 2008

Hi everyone. I am having difficulties with a cursor that I am trying to write. The purpose of the cursor is to loop through all tables in a selected Database and revoke "Select" access to that table for the indicated role (RoleToRevoke). I am getting the error on the @name variable within the REVOKE statement. I have placed a comment indicating where I am getting the error. Is there a way to have sql server interpret this @name variable within the REVOKE statement? Thanks for your help.




Code Snippet

USE [Database_Name];

Declare cursorExample Cursor for
Select TABLE_NAME
from information_schema.tables
Where TABLE_TYPE='Base Table'
and TABLE_SCHEMA='dbo'

Declare @name as varchar(255)
Declare @ErrorSave as int
Declare @ErrorCount as int

Open cursorExample

Fetch Next from cursorExample into @name


SET @ErrorSave = 0
SET @ErrorCount = 0




Begin Tran

While @@Fetch_Status=0

Begin

--Getting Error on Line below on @name
REVOKE SELECT ON OBJECT::@name FROM RoletoRevoke;

IF (@@ERROR <> 0)

BEGIN

Print 'Error Revoking Access to Table: ' + CAST(@name AS varchar(75))
SELECT @ErrorCount = @ErrorCount + 1
END
IF (@@ERROR = 0)

BEGIN

Print 'Successful Revoking Select Rights on Table: ' + CAST(@name AS varchar(75))








SELECT @ErrorSave = @ErrorSave + 1
END
Fetch Next from cursorExample into @name


End

IF @ErrorCount = 0

BEGIN

COMMIT TRAN
PRINT 'COMMITTED TRANSACTION'
PRINT 'Total Tables Affected:' + CAST(@ErrorSave AS varchar(75))
END
ELSE

BEGIN

ROLLBACK TRAN
PRINT 'ROLLED BACK TRANSACTION'
END

Close cursorExample
Deallocate cursorExample
GO

View 9 Replies View Related







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