Check User Authorization
Jun 16, 2008
I need to write a sp (for my web app) that checks user authorization. Here is the scenario:
User Authorization Table (AuthTable)
UserIDFrom Cost CenterTo Cost Center
(UserId)(FromCC)(ToCC)
User130003600
User148005120
User152005250
User230003400
User237603840
Production Table (ProdTable) data:
Job NumberCost CenterQuantity
(JobId)(JobCC)JobQty
51002860250
51003120525
51004801262
52003001155
52005251276
I need to check the user security for each record that I read from the ProdTable against all entries in the AuthTable for the current user.
When I read the ProdTable and User ID = User1 then selected records should be:
5100/3120, 5100/4801, 5200/3001,
When I read the ProdTable and UserID = User2, then the selected records should be:
5200/3120
I’m not sure how to construct my sql statement. Can somebody give me a hand? Thank you.
View 1 Replies
ADVERTISEMENT
Jun 5, 2008
my aspnetdb.mdf can,t created my remote connection settings in sql server is ok
and i get this folwing error :
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. SQLExpress database file auto-creation error:
The connection string specifies a local Sql Server Express instance using a database location within the applications App_Data directory. The provider attempted to automatically create the application services database because the provider determined that the database does not exist. The following configuration requirements are necessary to successfully check for existence of the application services database and automatically create the application services database:
If the applications App_Data directory does not already exist, the web server account must have read and write access to the applications directory. This is necessary because the web server account will automatically create the App_Data directory if it does not already exist.
If the applications App_Data directory already exists, the web server account only requires read and write access to the applications App_Data directory. This is necessary because the web server account will attempt to verify that the Sql Server Express database already exists within the applications App_Data directory. Revoking read access on the App_Data directory from the web server account will prevent the provider from correctly determining if the Sql Server Express database already exists. This will cause an error when the provider attempts to create a duplicate of an already existing database. Write access is required because the web server accounts credentials are used when creating the new database.
Sql Server Express must be installed on the machine.
The process identity for the web server account must have a local user profile. See the readme document for details on how to create a local user profile for both machine and domain accounts.
Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. Stack Trace:
[SqlException (0x80131904): An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)]
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +115
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +346
System.Data.SqlClient.TdsParser.Connect(Boolean& useFailoverPartner, Boolean& failoverDemandDone, String host, String failoverPartner, String protocol, SqlInternalConnectionTds connHandler, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject, Boolean aliasLookup) +1093
System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +1083
System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +272
System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +351
System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup) +90
System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +115
System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +122
System.Data.SqlClient.SqlConnection.Open() +227
System.Web.Management.SqlServices.GetSqlConnection(String server, String user, String password, Boolean trusted, String connectionString) +110
[HttpException (0x80004005): Unable to connect to SQL Server database.]
System.Web.Management.SqlServices.GetSqlConnection(String server, String user, String password, Boolean trusted, String connectionString) +181
System.Web.Management.SqlServices.SetupApplicationServices(String server, String user, String password, Boolean trusted, String connectionString, String database, String dbFileName, SqlFeatures features, Boolean install) +167
System.Web.Management.SqlServices.Install(String database, String dbFileName, String connectionString) +46
System.Web.DataAccess.SqlConnectionHelper.CreateMdfFile(String fullFileName, String dataDir, String connectionString) +681
View 1 Replies
View Related
Aug 1, 2001
dear all,
i need yours expertise to help me find out the number of user license in the Ms SQL 7. beside that, can i increase the number of user license manually.
Thank.
regards dennis
View 1 Replies
View Related
Jun 5, 2000
Can anyone out there help me write a sp to determine if a user has a certain role?
I'm trying to use "sp_helpuser @UserName".
Can I declare a cursor with "EXEC sp_helpuser @UserName"? I'm not having success with this.
What about SELECT [GroupName] FROM EXEC sp_helpuser @UserName WHERE [GroupName] = @GroupName? Again, syntax error.
Is there already a better way that someone knows of?
View 1 Replies
View Related
May 21, 2008
hi
i want when user connect to sql server and wants to do some works via my app, before doing nothing, check accessibility of user whether he/she can access that object or not, but i don't know which sp or function or view is useful for this work,can anyBody help me ?
thanks
View 6 Replies
View Related
Aug 5, 1999
Hi all,
When I use Administrator to login the NT, I can use the Enterprise Manager.
If I don't logout the NT, any users can easy to use the Enterprise Manager too.
I would like to know is there any way to add a security checking to avoid user without
privilege to use the Enterprise Manager?
Thanks
View 1 Replies
View Related
May 21, 2008
Hi
I've created a check constraint using the dialogue box in SQL Server , which blocks out the insertion of records which do not meet the required the requirements.
Is there a way to be notified when the record has not been inserted?
Thanks
View 1 Replies
View Related
Jun 20, 2008
How to find out whether a domain user has access to sql server or not?
Many domain groups have access to my sql server. I need to check whether a user has access to server or not.
Probably I need to check which windows group the user belongs. This looks more like an o/s question than DB. How do you guys manage this scenario?
------------------------
I think, therefore I am - Rene Descartes
View 4 Replies
View Related
Jul 15, 2015
we  do get incidents saying user can't login even adding to the group.So is there any script to check which group the user xxxxx belongs to from SSMS?
View 2 Replies
View Related
May 21, 2008
Hi,
When i restore a database from a different SQl 2005 instance to another,
i want to check if the logins are same and if the users in the destination db are all binded to the logins.
How can i do that?
Any help?
View 4 Replies
View Related
Jan 31, 2008
hi
can anyone help me out, i have one issue that is how i have to check while adding new user details to database table weather that user name is exist in that table or not.
for my application i'm using oracle database and C#.Net.
table is g_users and columns are:
Name Null? Type ----------------------------------------- -------- ---------------------------- USERID NOT NULL VARCHAR2(5) NAME VARCHAR2(20) EMPID VARCHAR2(5) USERNAME NOT NULL VARCHAR2(30) MOBILE NUMBER(12) EMAILID VARCHAR2(20) DOMAIN NOT NULL VARCHAR2(30)
i need to check from c# programming before inserting a new row into table weather username is already exists are not. how to check this one?
View 5 Replies
View Related
Jun 13, 2008
Hi there,
I was wondering if anyone knows how to do it.
We have a couple of servers, each of which hosts tons of databases.
I am trying to write a script to check whoever has logged into any database within the past week, for example (not just the current users). I have tried 'sp_who' and 'sp_who2' but they are not meeting my needs.
Appreciated!!
View 2 Replies
View Related
Apr 29, 2008
In my application (infopath browser forms), First I get current user name thru System.Environment.UserName, after that, i want to check whether this user has access (read & write) permission to certain database, How do I do that?
One way I tried before is to get all the users and stored the names in an array for comparing in my program, but can not figure out the SQL command (to get the database users), if there it is, please kindly tell me.
Or if I can just pass the current user to check?
Thanks in advance.
View 5 Replies
View Related
Oct 26, 2006
I have some script which creates/grant priveleges to windows/nt users to DB but i frequently get the message below:
Windows NT user or group '<user or group>' not found. Check the name again.
I understand that this is because the said user/group is indeed not present in the environment I'm running the script (ie. testing and production environment).
But is it possible to have some sort of checking whether the user/group exists in the environment so that I could determine whether or not to call some lines of code?
Not sure if this is possible so i had to ask. c",)
View 7 Replies
View Related
May 27, 2015
Is there a simple command that can be executed to check if the current user has sysadmin privs? Â I just want to check to see if I have sysadmin privs and if so then execute a command, if not do nothing in .Net code. Â I just want to do this check once and set a variable in the .net code.
View 4 Replies
View Related
May 3, 2012
I need query which will check user supplied date range is in between the existing table startdate and enddate.
if any of the date of user supplied date range is in between the tables start date and end date,it should retrun that record from table.
for example user supply date range is from 1 may 2012 to 5 may 2012.
then query must check that
1 may 2005
2 may 2005
3 may 2005
4 may 2005
5 may 2005
(all dates) is in between startdate and enddate of existing table.
View 2 Replies
View Related
Feb 26, 2006
I have installed visual studio 2005 with sql express. My tutorial book asked me to do the following to be able to use sql.
sqlcmd -S mycomputernameSQLExpress -E
which worked and then I get
1>
where I am to put in
1> sp_grantlogin [mycomputernamemyusername]
2> go
after this i get the error.
Msg 15401, Level 11, State 1, Server mycomputernameSQLEXPRESS, Procedure sp_grantlogin, Line 49
Windows NT user or group 'mycomputernamemyusername' not found. Check the name again.
I used Local System for the user name as that is what I think I am seeing as the right user account.
Help?
View 4 Replies
View Related
Mar 8, 2015
I've been running the Ola Hallengren maintenance script for the last five months without missing a beat. Today I find an error stating the UserDatabase Integrity check job failed last night. This is running on SQL Server 2014 BI edition w/64 Gigs.
I ran a DBCC CHECKDB on each database manually and all worked until I tried it on the biggest one that is about 18 gbs. It just keeps running and I eventually stopped it so I'm guessing it is memory, but doesn't make sense considering it has 64 gbs. I have it set to 64/4 max / min. Again, this was never an issue until last night.I've been looking up all morning, but not seeing much on this error "The operating system returned error 1453"?
View 5 Replies
View Related
May 18, 2007
i know the basics about how to manipulate SQL server database, i mean the code itself, but there are few other things which i don't understand.
first, databases which i have created by my own, i can use only when
i have the website itself open (i get an icon at the taskbar)
but database such as 'master' which came with the sqlserver, i can acess always even without running the page with visual studio first. i have no idea why.
also, when i try to connect my website using my ip, i can only do it with
the master database.. those i created by my own i can only connect when i enter the URL page as localhost.
I don't understand what's USER INSTANCE for and how do i really set
a database username and password
can someone exaplin a little? thanks, because i am very confused .
View 3 Replies
View Related
May 2, 2006
Hi:
I'm trying to connect to a data base of "sql server 2005 enterprise" named BCR.The server name is "server1"I'm using Windows Authentication to connect to serverThe operating system is Windows Server 2003 Enterprise Edition Service Pack 1
When I run the aspx I get this error at line 21
System.Data.OleDb.OleDbException: Invalid authorization specification Invalid connection string attribute at System.Data.OleDb.OleDbConnection.ProcessResults(Int32 hr) at System.Data.OleDb.OleDbConnection.InitializeProvider() at System.Data.OleDb.OleDbConnection.Open() at ASP.pruebaLeeSqlServer_aspx.__Render__control1(HtmlTextWriter __output, Control parameterContainer) in Z:DigitalpruebaLeeSqlServer.aspx:line 21
This is my code:
Dim str2 As String = "Provider=SQLNCLI; Server = server1; Database = BCR"Dim cnn2 As OleDbConnection = New OleDbConnection(str2)Dim cmd2 As New OleDbCommand()Dim drd2 As OleDbDataReaderTry cnn2.Open() cmd2.Connection = cnn2''''''''''''''''''''''''''''''''''''''''''''''''''''line 21 cmd2.CommandText ="SELECT LastName FROM Employees" drd2 = cmd2.ExecuteReader Do While drd2.Read %> <%=drd2.GetString(0)%><br> <% Loop drd2.Close()Catch exc1 As Exception %> <%=exc1.ToString()%> <%Finally cnn2.Close() End Try
What could be wrong?Do I need enable permission for asp.net in sql server?how I can do it?
Thanks!!
View 2 Replies
View Related
Jun 9, 2004
Hello,
I have a web application (ISAPI) that accesses a SQL Server 2000 (sp3) database in the same machine as the Web Server (IIS 6). Windows 2003 Standard Edition is the operating System. So, when users access the aplication through the web it runs ok until display the error message "Invalid authorization specification" or, sometimes, another message about failure to inform ODBC DSN, although I dont use ODBC.
When the error occurs, the user press F5 in the browser and the operation goes ok. It's an intermitent and curious error.
Can somebody help me?
Thanks in advance.
Adão.
View 1 Replies
View Related
Sep 16, 2015
I am trying to develope a authorization matrix for cpmpany of 75 employees, with different roles and permissions for roles to some applications like CRM , diiffrent folders,databases etc. I tried to find some informaton about this auhtorizaion matrix.
View 0 Replies
View Related
May 2, 2006
Hi:
I'm trying to connect to a data base of "sql server 2005 enterprise" named BCR.
The server name is "server1"
I'm using Windows Authentication to connect to server
The operating system is Windows Server 2003 Enterprise Edition Service Pack 1
When I run the aspx I get this error at line 21
---------------------------------------------------------------
System.Data.OleDb.OleDbException: Invalid authorization specification Invalid connection string attribute at System.Data.OleDb.OleDbConnection.ProcessResults(Int32 hr) at System.Data.OleDb.OleDbConnection.InitializeProvider() at System.Data.OleDb.OleDbConnection.Open() at ASP.pruebaLeeSqlServer_aspx.__Render__control1(HtmlTextWriter __output, Control parameterContainer) in Z:DigitalpruebaLeeSqlServer.aspx:line 21
---------------------------------------------------------------
This is my code:
------------------------------------------------------------------
Dim str2 As String = "Provider=SQLNCLI; Server = server1; Database = BCR"
Dim cnn2 As OleDbConnection = New OleDbConnection(str2)
Dim cmd2 As New OleDbCommand()
Dim drd2 As OleDbDataReader
Try
cnn2.Open()
cmd2.Connection = cnn2''''''''''''''''''''''''''''''''''''''''''''''''''''line 21
cmd2.CommandText ="SELECT LastName FROM Employees"
drd2 = cmd2.ExecuteReader
Do While drd2.Read
%>
<%=drd2.GetString(0)%><br>
<%
Loop
drd2.Close()
Catch exc1 As Exception
%>
<%=exc1.ToString()%>
<%
Finally
cnn2.Close()
End Try
------------------------------------------------------------------
What could be wrong?
Do I need enable permission for asp.net in sql server?
how I can do it?
Thanks!!
View 2 Replies
View Related
Jul 23, 2005
i want to implement authorization with windows authentication and don'thave the slightest clue of how to do this implementation. the basicwindows authentication for this .NET application is already setup. myproblem lies within my inability to manipulate the username captured inthe authentication process and my knowledge of how IIS is involved.specifically, i have the following questions:1) what object(s) can be used so that the user's username can bemanipulated for the authorization process?2) in order to apply roles, do the users need to be placed in groups inIIS? if so, how does this work?3) is all the code that the application uses for roles in web.config?or does global.asax play a role in this matter?4) does the web.config file know to communicate with IIS because theauthorization type is set to windows?5) once authorization is in place, can a section of an .aspx file bevisible to a group or can only entire files be secured for a group?as you can see, i'm trying to figure out the relationship betweenauthorization and windows authentication so any help would beappreciated.thanks,johne: Join Bytes!
View 1 Replies
View Related
Apr 23, 2007
Hi,
We are testing our product on Windows Vista with SQL Server 2005 SP2 and are encountering an "invalid authorization specification". We make the connection through a Windows service using a standard ADO connection. I have tried changing the provider in the connection string to SQLNCLI without any success. The application is written in Delphi 5 and has worked on previous versions of Windows running SQL Server 2005.
Thanks.
Steven
View 5 Replies
View Related
Sep 28, 2007
I know I'm missing something obvious and most likely just need some sleep to figure it out,but I'm trying to finish a block of auditing code off to hit a deadline. I can't figure out why I get an error when I try to do the following:
CREATE USER audituser WITHOUT LOGIN
GO
CREATE SCHEMA audit AUTHORIZATION audituser
GO
EXECUTE AS USER = 'audituser'
GO
--Create new, flexible audit structure
CREATE TABLE audit.AuditTable
(AuditID int identity(1,1),
TableName sysname NOT NULL,
AuditAction char(2) NOT NULL,
RowData xml NOT NULL,
AuditDate datetime NOT NULL CONSTRAINT df_table1_audit_editdate DEFAULT (getdate()),
AuditUser varchar(30) NOT NULL CONSTRAINT df_table1_audit_edituser DEFAULT (suser_sname()),
CONSTRAINT pk_table1_audit PRIMARY KEY CLUSTERED (AuditID))
GO
The user and schema are created properly. audituser is the owner of the schema and therefore should have the authority to do anything at all within the schema. However, the create table statement is failing and saying CREATE TABLE permission denied. Why?
View 1 Replies
View Related
Aug 1, 2007
Hi Guys
Hoping that someone out there is able to give me some help on this one
I am trying to install SQL Server 2005 developer Edition on to a new laptop that is running Windows vista home premium edition
However after running the install and then trying to run the Service Pack 2 it producing an error saying that the windows authorisation is not valid and not using named pipes protocol. After some searching and googling I have checked all the option that I can see like that my login to the windows environment is the administrator which its is but from there I still am unable to log
Any Ideas people, or is this a known problem that I am just not seen
View 10 Replies
View Related
Jan 8, 2007
Hello,I have an SQL Server 2005 database that I'm trying to create using script.Everything works fine, but when I click on "Database Diagrams" I get anerror message that there is no database owner, and of course when I show theDatabase Properties, the database was created without one. Is there any wayto ALTER AUTHORIZATION to a default owner or system administrator, somethingvalid?Any direction would be appreciated.Thanks!Rick
View 1 Replies
View Related
Nov 28, 2007
I am in the planning phase of building a Form Authenication Report Server project. The database contains the Role information for each report and for each user. The objective is to authenicate and authorize user so that only the authorized reports are available for the current user to view.
Base on my research these are my plans.
1. Deploy Reports onto the Report Server
2. Put Reports in folders to group different types of report (I am not sure is it possible to only the display authorized reports for the current user)
3. Design the custom Security extension to authenicate and authorize using the database
1. Is it possible to only display authorized reports for the current user to view by doing custom security extension?
View 5 Replies
View Related
Dec 4, 2006
For custom authorization, I am overwriting the CheckAccess method.
But how do I know what I am trying to access in CheckAccess method? I need to know what report the check access method is trying to execute for our requirements.
How do I get that?
View 1 Replies
View Related
May 15, 2007
Hi,
I have an MS Access front end that links to an sql server 2005 database. The access front end uses NT Integrated security which should authenticate and authorize users.
Unfortunately it is not working this way. I have domain users (Active Directory) which I have given permissions to on my database and have given them, say db reader or db writer previliges.
Not only do these previliges NOT work, any user that is on the network can access the database and can read/write onto it as long as he has the MS Access front end.
I'm pulling my hair (and I dont have much left) trying to figure out what I am missing.. or if I even have the whole NT integration logic right.
One point worth mentioning is that if I change the connection type to 'SQL password authentication', the MS access front end prompts for a username and password and user permissions are set just they way i have set them in sql server 2005. But the only problem with this is that any person can just go and change the connection property in the MS Access front end back to NT integration and he/she can access the database with no restrictions whatsoever.
Where am I going wrong. HELP!
View 5 Replies
View Related
Jan 16, 2007
I need to enable digest authorization on an HTTP endpoint, because the integration tools for some java code that needs to connect to it does not support NTLM or kerberos - only digest and basic. I setup a test endpoint in order to test only digest authorization (as opposed to the final endpoint which needs both digest and integrated), but I can't even get that to work - when I point my browser to it to see the wsdl it pops up with the login box, but the login always fails.
I have the feeling I'm missing some very minor piece that will just make the whole thing work. Below are some log entries as well as the script to create the test endpoint I'm hitting. Any ideas are very welcome, I've banged my head on this one for far too long.
SQL Server error logs shows a "Error: 26026, Severity: 14, State: 1" entry followed by a "HTTP authentication failed" entry.
Event Log Security entry on the server:
Logon Failure:
Reason: An error occurred during logon
User Name: MyUserName
Domain: mydomain
Logon Type: 3
Logon Process: WDIGEST
Authentication Package: WDigest
Workstation Name: -
Status code: 0xC000006D
Substatus code: 0xC00000FE
Caller User Name: -
Caller Domain: -
Caller Logon ID: -
Caller Process ID: -
Transited Services: -
Source Network Address: -
Source Port: -
Test endpoint script:
CREATE ENDPOINT [DigestTest]
STATE=STARTED
AS HTTP (PATH=N'/DigestTest', PORTS = (CLEAR), AUTHENTICATION = (DIGEST), SITE=N'SRV001',
CLEAR_PORT = 80, AUTH_REALM=N'mydomain', COMPRESSION=DISABLED)
FOR SOAP (
WEBMETHOD 'SimpleTest'( NAME=N'[TestDB].[dbo].[spSimpleTest]'
, SCHEMA=DEFAULT
, FORMAT=ALL_RESULTS),
BATCHES=DISABLED, WSDL=N'[master].[sys].[sp_http_generate_wsdl_defaultcomplexorsimple]',
SESSIONS=DISABLED, SESSION_TIMEOUT=60, DATABASE=N'TestDB', NAMESPACE=N'http://mydomain.com/',
SCHEMA=STANDARD, CHARACTER_SET=XML)
View 6 Replies
View Related
Oct 3, 2007
I need to implement custom security in reporting services. I have followed the standard example here:
http://technet.microsoft.com/en-us/library/ms160724.aspx
I got authentication to work without too many problems, and it authenticates by querying my custom table. I want to also maintain my authorizations in the same manner. I want to be able to define in my own tables that user test1 has permission to view reports 1, 3, 5, and 8. User test2 can view reports 1,3,5,8, and 9. User test3 can view 1, 2, and 4. Etc.
The authorization extension, contained in my version of Authorization.cs is where I expected to be able to do this. I wanted to be able to write code that queries my own tables and says, "does this user have permission to view this item?" Getting the this user portion is easy as is obvious in the many checkAccess methods. But being able to tell what item I am currently dealing with has proved impossible from the context of Authorization.cs, which implements IAuthorizationExtension. Knowing what type of item I am dealing with is obvious, but I need to know something that uniquely identifies the exact item I am dealing with.
How can I determine in checkAccess or any of the other available methods what "thing" they are looking at? What can I examine to determine that for instance this particular authorization request is against "Report 1"? It seems that if I was allowed to completely override Authentication to use my tables, I should be able to override Authorization to do the same. Is this possible or am I missing something?
Can anyone help? Thanks.
View 2 Replies
View Related
