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


ADVERTISEMENT

Restrict Role To CREATE TABLE On An Assigned SCHEMA

May 9, 2008

Can anyone help me with this? The objective is to allow an application user (with db_datareader & db_datawriter database roles assigned) to be able to create tables in the assigned schema (dbo) via a new role.


-- Create User

use master

go

create login DBA with password='xx', CHECK_EXPIRATION=ON, CHECK_POLICY=ON

use AdventureWorks

go

create user dba from login DBA
alter user dba with DEFAULT_SCHEMA=dbo

go

-- Create Role

use AdventureWorks

go

create role sp_ddl_role AUTHORIZATION dbo

grant CREATE TABLE to sp_ddl_role



-- assign user to Role

use AdventureWorks

go

exec sp_addrolemember sp_ddl_role, dba

go

-- Create Table statement then run with following error
Error:

Msg 2760, Level 16, State 1, Line 1

The specified schema name "dbo" either does not exist or you do not have permission to use it.

Remedy: Grant ALTER on schema::dbo to sp_ddl_role

My problem is that I want to restrict user permissions via this role to just the CREATE TABLE and granting ALTER on a SCHEMA will open up a whole lot more permissions from a security standpoint.

Question: How do I restirct this role to just the CREATE TABLE within a SCHEMA?

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

Accessing Windows Users Assigned To A Database Role

Mar 3, 2006

Is
there a way to find a list of Windows User accounts that are directly
or indirectly (through Windows Group membership) assigned to a database
role?


I could put work in to CLR programming or using a Linked Server to
Active Directory, but if there is a sys.* view available that can
provide me this
information directly it would be much easier.



I'll be looking into this further myself anyway and posting an answer
if I can find one, but if anyone has suggestions... well, thanks!

View 1 Replies View Related

Public Role And Guest Security Concern In SQL 2000 SP4

Sep 8, 2006

Hi all,

I have setup a new SQL 2000 SP4 and internal auditor query about revoke permission from Public role and remove guest from all databases.

1. Can I revoke all default permissions (select on system tables in all DBs) from "Public" role? I am concern any error after such action.

2. I found that guest account in DB -- master, tempdb and msdb. According to Microsoft documents. The account should not remove and can't from master and tempdb. How about msdb?

Thanks,

Regards,

Edwin

View 7 Replies View Related

Mirroring :: Principal Database Get Role Back After Being Back On Line

May 14, 2015

New to Database Mirroring and I have a question about the Principal database server. I have a Database Mirroring setup configured for High-safety with automatic fail over mode using a witness.

When a fail over occurs because of a lost of communication between the principal and mirror, the mirror server takes on the roll of Principal. When communication is returned to the Principal server, at some point does the database that was the previous Principal database automatically go back to being the Principal server?

View 2 Replies View Related

Guest Access?

Oct 25, 1999

If our SQL Server is not part of a domain, can "Guest" users still connect to the SQL server?

What we are experiencing is -- when a drive is mapped to the server connectivity is fine.
But, without the drive mapping, the SQL connections cannot be made.

Thoughts, Ideas,

(hopefully without adding unique logins for each user at the server)

View 2 Replies View Related

SQL 2012 :: Report GUEST User Access Status In All Databases On Instance Level

Apr 17, 2015

Below query tells us if guest user is enabled or disabled in a particular database

SELECT dp.name, CASE perms.class WHEN 0 THEN 'Yes' ELSE 'No' END AS 'Enabled'
FROM sys.database_principals dp
LEFT JOIN (SELECT grantee_principal_id, class FROM sys.database_permissions
WHERE class = 0 AND type = 'CO' AND state = 'G') AS perms
ON dp.principal_id = perms.grantee_principal_id
WHERE dp.name = 'guest';

Do we have a query which can also add the database name to above query output? The output must have columns with data against Name,Enabled,Database name

View 1 Replies View Related

The Server Principal XYuser Is Not Able To Access The Database Ydb Under The Current Security Context

May 23, 2007

SQL2005 on winserver 2003. I have a view in Xdb that accesses tables in 2 different databases (Xdb and Ydb) on the same server. I have mixed mode security. I have a SQL user (XYuser) that has read access to all tables and views on both databases, yet when I try to access the view using a C# windows application I get the following error:



The server principal "XYuser" is not able to access the database "Ydb" under the current security context



This same scenario works under SQL 2000. I looked through the postings and tried to set TRUSTWORTHY ON on both databases but that didn't help. I can access any other views or tables on the SQL 2005 server, just not the one that joins the tables cross databases. Any help is much appreciated... john

View 17 Replies View Related

SQL Tools :: Error - Unable To Access User Principal Information From The System

Aug 25, 2014

I am not able to launch the Kerberos Configuration Manager due to the above error.

View 4 Replies View Related

Windows Server Principal Denied Access To Database Under Current Context

May 1, 2007

Hi guys. I created a database "MyDatabase" using an AD Account "user01" which is a member of a domain group "GroupA". I have created a login in my SQL Server 2005 for this AD group. I have also added the said login as a user of my database. I created other AD users and put them in the same group. When I try to access the database using the other users, I get this message

The server principal "MyDomainuser02" is not able to access the database "MyDatabase" under the current security context.

This KB tells me that my situation is triggered by the first case in which the owner of the database is windows authenticated. I have already set the database to be TRUSTWORTHY. I haven't tried CERTIFICATES though. I believe I haven't done anything complex to warrant this solution.

I'm really lost with the myriads of principals and securables that SQL2005 has. All I want is just to execute a certain set of stored procedures. I can't do this because I can't even execute "USE MyDatabase". What are the permissions I need to give to "GroupA" ?

View 5 Replies View Related

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

DataReader Access

Feb 22, 2007

Hi,
I am facing a problem to access datareader... actually i want to get data on lables from datareader. actually i am having one table having only one column and i hav accessed all data into datareader but the problem is that i just want to get data row by row...
 
For example there are four labels Label1, Label2, Label3, Label4
and want to print the data from datareader on to thease labels....
 
 
plz do reply... i am in trouble

View 1 Replies View Related

Principal Db In Principal, Synchronizing State, The Mirror In Restoring State

Sep 26, 2007

We have mirroring setup for 5 dbs, 4 of which are synchronized and 1 which is in "synchronizing" state on the principal and "restoring" state on the mirror. Mirroring for all dbs has been working fine for the past several months and we have a witness that has allowed automatic failover in the past without problems.

This database has several bulk inserts performed throughout the day and am sure there is some latency due to the size of these transactions.

Not sure as to why this is happening all of a sudden, but the db in question has been in this state for the past 12 hrs. I checked the mirroring status on the principal and it states that it is "synchronizing: data is being transferred from principal to mirror", but the mirror server states that db is in "restoring" state. Can anyone suggest as to how I can get the database on the mirroring server to get back to "mirror, synchronizing/restoring..." state? Or suggest on how I can troubleshoot this?

Thanks in advance.

View 3 Replies View Related

Access To Datareader Destination On A Remote Package

Jan 22, 2008

I'm fairly new to SSIS, and I'm looking for some general guidance on the best approach to take to a particular issue. Essentially, I've built a web application, running on a remote server, and I'm looking to take the contents of an Excel spreadsheet, load it into memory, run a whole mess of calculations against it, and then load the results into a table on MS-SQL 2005. The calculations are in a class library, written in C#, so my sense was that the best approach would be to a) load the data into a datareader, b) run the calculations against the data and c) load it to SQL. Of these steps, a) seems like the place where SSIS would come into play.

My question is, once I've got the data into a datareader, how can it be accessed from my code? I'd been playing around with DtsClient until having the realization that it was intended to be used locally. Is there a similar method for doing this sort of thing remotely?

View 3 Replies View Related

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

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

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

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

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

Transaction Rollback Is Used Before It Is Assigned A Value

Apr 6, 2006

I am moving some code that was created in visual studio 2002 into Visual Web Developer 2005 express edition and am getting a warning on some of my code before compilation.
The warning is "Variable lSqlTransaction is used before it has been assigned a value.  A null reference exception could result at runtime".  I experienced no problems in vs2002.
The offending line is the rollback command within the exception.  If I move it out of the exception it no longer flags the warning, if I move it after the throw statement it now longer flags the warning.  Any help appreciated.
Heres the code
  Public Shared Sub ExecuteNonQuery(ByVal lSQLCommand As SqlCommand)

Dim lSQLConnection As New SqlConnection(SQLConnString(enmSQLDB.enmSQLDB_TPSDB))
Dim lSQLTransaction As SqlTransaction

Try
'-----------------------------------------------------
'Try and open the database
'-----------------------------------------------------
lSQLConnection.Open()

'-----------------------------------------------------
'Create a transaction
'-----------------------------------------------------
lSQLTransaction = lSQLConnection.BeginTransaction

'-----------------------------------------------------
'Assign the connection and transaction to our object
'-----------------------------------------------------
lSQLCommand.Connection = lSQLConnection
lSQLCommand.Transaction = lSQLTransaction

'-----------------------------------------------------
'Execute the stored proc
'-----------------------------------------------------
lSQLCommand.ExecuteNonQuery()

'-----------------------------------------------------
'Use the transaction to commit the changes
'-----------------------------------------------------
lSQLTransaction.Commit()

Catch ex As Exception
'-----------------------------------------------------
'If any errors have been thrown then roll back without
'commiting....
'-----------------------------------------------------
lSQLTransaction.Rollback()

Throw New Exception("An error has occured whilst trying to update the database. " & _
"Your changes have not been saved.", ex)


Finally
If lSQLConnection.State = ConnectionState.Open Then
lSQLConnection.Close()
End If
End Try

End Sub
 

View 1 Replies View Related

Filegroup %f Has No Files Assigned To It.

Nov 20, 2007

I'm tryin to copy information from a fact table from one server to another server but when try using a DTS package to do this, an error pops up that says "the Filegroup <filegroup_name> has no files assigned to it."

what am i missing here?

View 4 Replies View Related

Trigger-assigned Value Changes When Row Replicates - Why?

Feb 14, 2008

Greetings,

We have a standard audit trigger on one of our tables. The trigger type is "after insert, update". The trigger populates four table columns, telling us the login and time the row was created and last updated.

We use replication to synchronize three servers. The trigger specifies "not for replication" on all servers.

The code used to grab the identity and time of the last update is shown below.


LastEditedDate = GETUTCDATE(),

LastEditedBy = SUSER_SNAME()

What I observe is that the LastEditedBy value is sometimes different for the same row on different servers even though the time value is identical. I don't know how to explain this. It looks like the login value is being recalculated during replication while the edit time is not.

For the record, the correct login will be something like "MYDOMjoe" while the incorrect value on another server will read "NT AUTHORITYNETWORK SERVICE". Do these symptoms ring a bell with anyone?

Thanks,
BCB

View 5 Replies View Related

SSIS Assigned Datatype

Apr 23, 2007

I have a problem with datatype mismatches. In my Data Flow I am reading a Flat File that includes a DateOfBirth which SSIS correctly assigns a datatype of [DT_DATE]. I need to check to see if the record has previously been submitted, so I do a Lookup that uses a SQL statement that combines fields (LastName, Zip & BirthDate) from three different tables. My problem is that SSIS assigns a datatype of [DT_DBTIMESTAMP] to BirthDate, which I can't use because of the mismatch. I have tried every form of re-formating the field in SQL that I can think of, but it still gets assigned that DT. And I'm unaware of a way of changing the DT once SSIS assigns it in the Lookup task.



Help!...very frustrated,

Dave

View 3 Replies View Related

Can A Value Be Assigned To An Output Parameter And Be Used In A Where Clause?

Dec 18, 2006

I need to update a table using a stored procedure that "UPSERTS". In this case, @OfficeId is passed with ParameterDirection.OutputSo when I update I assign the value to OfficeId like this (the office id value is being correctly assigned): SqlParameter OfficeIdParam = new SqlParameter("@OfficeId", SqlDbType.Int, 4);            if (_officeId > 0) { OfficeIdParam.Value = _officeId; }            OfficeIdParam.Direction = ParameterDirection.Output;   The following segment of the stored procedure is throwing an error that the OfficeId does not exist: Update --- @OfficeId int  output if not exists (select 1 from ORG_Office where OfficeId=@OfficeId)    begin        RAISERROR ('OfficeId does not exist in ORG_Office: E002', 16, 1) -- OfficeId does not exist.        return -1    end May this approach work, and if the operation turns out to be an Insert I may do this:select @error_code = @@ERROR, @OfficeId= scope_identity() Thank you,jspurlin 

View 1 Replies View Related

Multiple Variables Assigned To One Select

Apr 6, 2006

Hello,
Is there a way to assign multiple variables to one select statement as in the following example?
DECLARE @FirstName VARCHAR(100)
DECLARE @MiddleName VARCHAR(100)
DECLARE @LastName VARCHAR(100)
@FirstName, @MiddleName, @LastName = SELECT FirstName, MiddleName, LastName FROM USERS WHERE username='UniqueUserName'
 
I don't like having to use one select statement for each variable I need to pull from a query.  This is in reference to a stored procedure.
 
Thank you!
Cody

View 1 Replies View Related

Row Number Assigned Via RunningValue But Without Grouping

Jan 10, 2007

I have a report where certain columns have values that get repeated, but the client has a rigid requirement for not wanting these columns as groups in the reports (other programs and exports doing specific tasks with those values). In these reports, the "hide duplicates" value is checked, so as to give an aesthetic sense of grouping.

The data for the report, in raw form is:








Value 1
A

Value 1
B

Value 1
C

Value 2
D

Value 2
E

Value 2
F

Value 3
G

Value 3
Hetc...

In its aesthetic form, it is:








Value 1
A


B


C

Value 2
D


E


F

Value 3
G


H

My question is, how can I use RunningValue() to give me the following row numbers based upon those repeated values acting as grouping, since I'm not using formal grouping in the report design?











Value 1
1
A


2
B


3
C

Value 2
1
D


2
E


3
F

Value 3
1
G


2
H



Thanks for any input!

View 3 Replies View Related

How Are Device Cals Managed And Assigned.

Aug 21, 2007

Hello all,

This seems like a simple question but I still haven't been able to get an answer from Microsoft Canada. We're looking at buying 75 device cals to connect to a single instance of SQL Server Standard Edition. Our question is on how those licenses are assigned and/or activated.

Do we need to manually do something for each station or is it an automated process done the first time a station connects to the server? We also wat to understand what happens when a workstation dies and is replaced by another one. Do you have to do something on the server to transfer the license to the new station ?

In other words, when using device cals (and/or user cals), do you have to do any type of license management or is it all transparent ?

View 1 Replies View Related

Why Is Severity 20 Assigned To SSPI Errors

Aug 28, 2007

At our site, we page DBAs whenever an alert with a severity of 20 or greater is raised. This never was an issue until SQL Server 2005. Now every SSPI error is paged since it is a severity 20. I don't believe the errors are severe enough to qualify an assignment of 20. Anybody else have any thoughts on this?

Thanks.

View 4 Replies View Related

Rights Assigned To WindowsPower Users In SQL Express.

Nov 9, 2005

Hi,
In SQL Server Express Edition, what are the rights that are assigned to a Normal Windows User and PowerUser by default ?
When I install SQL Express on a clean machine and login as Power User I can add/edit/delete data but when I login as Normal Windows User I can see the data but not change it.
Please help me in this regard.

View 1 Replies View Related

Has SELECT INTO With IDENTITY() Function Changed The Way #'s Are Assigned?

Oct 10, 2007

We have SELECT INTO queries that use the IDENTITY function to assign a sequential row number to a result set based upon a sort order. This has been helpful in SQL Server 2000 for median determination. It appears, however, that in SQL Server 2005, the row numbers are not assigned sequentially, or maybe they are assigned before the sort order is applied.

Can anyone verify whether the IDENTITY function has changed behavior between 2000 and 2005? We would prefer not to have to make changes to existing queries. Thank you.


(BTW, the workaround we found so far is to put the initial SELECT...ORDER BY in a subquery, but then we had to include a phrase like TOP 10000000 to pass syntax check. Is there a better way?)

View 4 Replies View Related

How Do I Change The Status On My Database? Currently Is Is Assigned As Restricted User

Jun 19, 2008

I restored a  sql server database today. There is a problem though. In SQL server, right next to the name of the database are the words "Restricted User". Their seem to be some limitations on my access to this database now as I am not able to run some of my C# code against the database. How can I fix this in SQL server so that I am not a Restriced user.
 
Example of what I am talking about. In SQL server my database is named Gorlaz. Just to the left of this is a yellow Icon that represents the database "Gorlaz". For illustration purposes I will use the character "$" to substitute for the yellow database icon. So this is how it reads in SQL server management studio.
$Gorlaz(Restricted User)
 

View 3 Replies View Related







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