How To Discover Either Windows Group Or SQL Login

Jul 20, 2005

Hello,

I am putting together a row level security plan for our sales
database. I will give a brief description of the method I am thinking
of using to give you an idea of how I will need to be able to discover
the group or login the user is using to access the data.

I have a table called salesfact, it has all the sales info for all the
branches of our company. Each order(row) that is inserted has an entry
in the division_number column to describe which branch the order
belongs to. I have created another table(Branch_Folks) that has four
columns; username, windows_group, SQL_Login and division_number.

I am using a view and SQL logins to control access to the data based
on the user, the SQL logins give windows groups access to the view.
Only users that are added to the specific branch groups will have
access to the logins, but if a user is added to the group without me
being notified, then he will not have a corresponding entry in the
Branch_Folks table. Currently I am using the SUSER_SNAME() function to
determine which user is accessing the data from the view that I have
created. The view uses this select statement to filter the data based
on the user & division.

Select * from tsalesfact A, Branch_Folks B where SUSER_SNAME() =
B.username and A.division_number = b.division_number

This method works fine, but I will have to manually maintain the user
list in the Branch_Folks table in case a new user joins the windows
branch group. I would like to use a function similar to SUSER_SNAME()
that can determine the windows group or SQL Login a user is using.

Does anyone know of a way to do this??

Thanks a ton,

Tim

View 1 Replies


ADVERTISEMENT

Cannot Get CREATE LOGIN From A Windows Group To Work

Jan 28, 2007

I have created a database fronted by an ASP.Net application. It's all nice and simple, and I only need a very simple level of security (and even that is only as a protection against accidents rather than maliciousness). My intention is that users connect using Windows impersonation (<identity="true">), with the database creator having full access and the public group (I'm talking SQL groups here) having specific premissions granted on specific tables.

If I set <identity="false"> on my XP box the application connects to the database as [MACHINEASPNET]. This is easy to set up access for - I simply do a

CREATE LOGIN [MACHINEASPNET] FROM WINDOWS

and then within the actual database do a

CREATE USER [MACHINEASPNET]

But as I said, I want to use Windows impersonation. When I set <identity="true">, the application correctly attempts to connect as the actual Windows user account (e.g. [MACHINE estuser]). If that user is the user who installed the database, then all is well and it has full access. For anything else, I get a "cannot log on" error - this much I expect.

So I want to permit logins for all other users, and I want this to work regardless of whether the machine is a standalone machine whose "domain" is simply the machine's own name, whether it is in some form of traditional peer-to-peer workgroup, or whether the machine is connected to a real domain. I also want it to work on XP and Windows Server 2003 (and ideally Vista also, but that can wait). When I try the following:

CREATE LOGIN [MACHINEUsers] FROM WINDOWS

I get this error:

Msg 15401, Level 16, State 1, Server MACHINESQLEXPRESS, Line 1
Windows NT user or group 'MACHINEUsers' not found. Check the name again.

Nor does it work with [Everyone] (that one has no domain/folder listed against it in any permissions dialogs on my domainless development PC). So I'm stuck and confused. It's taken me ages just to get this far. Any suggestions anyone?

Thanks in advance.

View 8 Replies View Related

Use Windows Group As Local Login For Linked Server?

Mar 12, 2008

Hi all:

I have created a linked server that connects a SQL 2000 database to a SQL 2005 database. If I use individual SQL or Windows accounts as local logins on the SQL 2000 instance, I can successfully query the linked SQL 2005 database.
(For security we use the setting "For a login not defined in the list above, connections will: not be made")

If I try to use a Windows group as the local login, remote queries fail with the error
"Access to the remote server is denied because no login-mapping exists"

Is it not possible to use a Windows group for the local login of a linked server?

If I run 'exec sp_linkedservers' the setup appears valid

Linked Server Local Login Is Self Mapping Remote Login
SQL2005Serv DomainBRubble 0 SQL_Read_Access
SQL2005Serv DomainWindows_Group 0 SQL_Read_Access
SQL2005Serv DomainFFlintstone 0 SQL_Read_Access

Thanks in advance
Grant




View 5 Replies View Related

Cannot Connect To Localhost Using Windows Security Group As Login

Feb 19, 2007

I have a test environment set up at home with a small windows server 2003 Active Directory domain. I created a windows security group on the dc and added myself and another user to the group. I then use this group as a login for SQL Server and add the login to the sysadmin server role. This works exactly as I would expect on the Windows Server computer (which is also the Domain Controller) regardless of which computer I use to connect to this Windows Server computer. However, when I set up the exact same login on the local sql server instance of my client laptop running windows xp pro, the I get an error message (Login failed for user MYDOMAINdgolds...) when I attempt to connect to the local instance of SQL server, even though I am able to connect to the instance on the domain controller with no problems using this same client laptop under the same user account. The only way I am able to connect locally on the laptop under this account (which does not have local admin privileges on any box, for testing purposes) is to add the user account separately as a login, rather than as part of a group. I'm curious as to why being a member of a security group that is part of the sysadmin server role does not allow me to connect to the local instance of SQL Server on this client laptop. The windows server computer is running SQL2k5 Enterprise, and the client laptop is running SQL2k5 developer. Any insight into this would be appreciated.

Thanks,

Dave

View 12 Replies View Related

SQL Security :: Replacing Windows Active Directory Group Instead Of Login?

Oct 1, 2015

Current: One common SQL login is being used by SQL DBA on all the servers

New Plan: Creating one windows AD group, adding the DBA's to that group and create as a login with sysadmin server access on all the SQL Server boxes

how to achieve this activity. Creating SQL login is fine but how to change the ownership of various objects, jobs to new login on all servers?

View 3 Replies View Related

Error 15401: Windows NT Group Or Group Not Found

Sep 25, 2003

I have a user in SQL Server with a NT login of Mike
I changed his NT account to Mikel in User Manager

Now when I try to add Mikel, Im getting error 15401.

Do I need to delete NT login in SQL Server 'Mike' account first ?..before adding 'Mikel' ?

Can I go into the Master database and just change Mike login to Mikel ?

Thank you

View 3 Replies View Related

SQL Security :: Users Are Able To Login To Server Without Any Login Names Or Being Part Of A Group

Jun 5, 2015

I have a server that has 20 databases . I have tested with few users with different level of access and all of them were able to connect to the server and also see, select, update , delete from a particular database which is kind of weird because they do not have a user login associated or mapped to that database. I checked and no user is part of any group in AD that would give them permission to connect . I need a query that would find the permission path of a user. I already queried with xp_logininfo but I am not getting any thing.

View 9 Replies View Related

Login For Domain Local Group And Global Group

Jan 5, 2008

I have one domoain in the forest. The domain level is set to Windows 2000 native mode and forest level is set to mixed mode. My SQL server 2005 server joined to this domain. I added a brand new domain local group and add a normal user account to this domain local group. I login to the SQL server 2005 server and make a query "SELECT * FROM sys.login_token". I cannot see my domain local group in sys.login_token. However, if I add my account to a global group, I can see it there.

Then, I setup another forest. This time, I have domain level set to Windows 2003 mode and forest level is set to Windows 2003 native mode. I do the same testing. This time, I can see my domain local group in sys.login_token.

Why does SQL server 2005 has this limitation? Is it a bug?

View 1 Replies View Related

Discover Relationships Between Existing Tables Of A Database

Mar 23, 2007

Hello Friends,
I am right now working on a project that has a database with over 100 tables in a database. Because of extreme time constraints the developers didn't build in any relationships or constraints between or in the tables. Now I need to remodel the database such that the database is more structured and normalized. I don't have much knowledge about the database design since it is a 2 year old application and the person who developed the database is now gone. I know remodelling the database would require knowledge of the existing database and business rules.
I was wondering if there are any tools that could suggest or discover relationships between tables. For eg. Lets say there are two tables named 'Customer' and 'Order'. I notice that there is a column named 'id' in Customer and a column named 'customer_id' in Order. So I ask the tool to discover a relationship between id and customer_id and it tells me that there is a one-one or one-many or no relationship by comparing values. I heard ERWin would be able to do that but thats expensive. Please do let me know asap.

View 2 Replies View Related

Snapshot For Merge Replication Error: Discover Dependencies Failed

Mar 1, 2006

I'm converting an existing production replication process to SQL 2005 for testing. My snapshot for a merge replication is failing.

Environment: Single XP server, no filters, @schema_option = 0x000000000C034DD1

I have several snapshot jobs in my process, 2 for transactional replication and 2 for merge replications. In both types, I'm replicating from A to B and then from B to C. In the failing job, I'm merge replicating from B to C. In this failing snapshot job, the error messages are:

2006-03-01 18:42:17.22 [58%] Bulk copying snapshot data for article 'MyName1'
2006-03-01 18:42:17.34 [58%] Bulk copied snapshot data for article 'MyName2' (0 rows).
2006-03-01 18:42:17.41 [58%] Bulk copied snapshot data for article 'MyName1'(195 rows).
2006-03-01 18:42:29.84 [61%] Discovering dependencies among article objects in the specified publication
2006-03-01 18:42:33.66 [61%] The replication agent had encountered an exception.
2006-03-01 18:42:33.66 Source: Unknown
2006-03-01 18:42:33.66 Exception Type: Microsoft.SqlServer.Management.Smo.FailedOperationException
2006-03-01 18:42:33.66 Exception Message: Discover dependencies failed.
2006-03-01 18:42:33.66 Message Code: Not Applicable
2006-03-01 18:42:33.66


Does anyone have an idea what I could look for to try and fix this?

The A to B snapshot obviously uses the same tables. My snapshot for merge job (which worked) messages for the A to B snapshot are:

Message
2006-03-01 18:40:24.98 Time spent analyzing foreign key references (seconds): 0.00
2006-03-01 18:40:24.98 Time spent analyzing check and default constraint references (seconds): 0.19
2006-03-01 18:40:24.98 Time spent analyzing non-article object dependencies (seconds): 0.42
2006-03-01 18:40:24.98 Time spent preparing snapshot generation (seconds): 35.49
2006-03-01 18:40:24.98 Time spent generating merge replication procedures (seconds): 41.39
2006-03-01 18:40:24.98 Time spent generating merge replication stored procedure scripts for subscribers (seconds): 20.66
2006-03-01 18:40:24.98 Time spent bulk copying data (seconds): 1.25
2006-03-01 18:40:24.98 Time spent copying/compressing generated file (seconds): 0.00
2006-03-01 18:40:25.00 Time spent adding rowguid columns/creating merge triggers (seconds): 1.38
2006-03-01 18:40:25.00 Time spent posting snapshot commands (seconds): 25.97
2006-03-01 18:40:25.00 *******************************************************************************

 

I kicked off the job again and got a different error message:

Message
2006-03-01 19:17:30.32 ---------------------------------------------
2006-03-01 19:17:30.32 -BcpBatchSize 100000
2006-03-01 19:17:30.32 -HistoryVerboseLevel 2
2006-03-01 19:17:30.32 -LoginTimeout 15
2006-03-01 19:17:30.32 -QueryTimeout 1800
2006-03-01 19:17:30.32 ---------------------------------------------
2006-03-01 19:17:30.32 Connecting to Publisher 'xxx-xxxxxxxx'
2006-03-01 19:17:30.34 Publisher database compatibility level is set to 90.
2006-03-01 19:17:30.36 Retrieving publication and article information from the publisher database 'xxx-xxxxxxxx.xxxxxxxxx'
2006-03-01 19:17:30.54 [0%] The replication agent had encountered an exception.
2006-03-01 19:17:30.54 Source: Replication
2006-03-01 19:17:30.54 Exception Type: Microsoft.SqlServer.Replication.ReplicationAgentSqlException
2006-03-01 19:17:30.54 Exception Message: Data is Null. This method or property cannot be called on Null values.
2006-03-01 19:17:30.54 Message Code: 52006
2006-03-01 19:17:30.54

Does that help provide a clue?


 

View 7 Replies View Related

SQL Server 2014 :: Can Get User Windows Login When Using Server Login Authentication

Apr 26, 2015

Oracle and Teradata are able to capture the windows/client login that connects to their database with a functionnal ID.I've searched around and I don't think MS SQL can do this...I administer SQL 2014 server and also SAS Enterprise guide product. I've created sql server logins for several teams so everyone shares the same sql server login which is used to bind the SAS library to my SQL server database.

However, when looking at activity monitor, I can't drill down to which user is executing a particular query. I only see the SQL login which is share amongs 200 other folks.On the other hand, when they connect from SAS to Oracle or Teradata, I can see the shared login + additionally their windows NT login used to connect to SAS which is awesome....they can't fool me by using a shared/funcionnal ID....i still see who's using that ID...

View 2 Replies View Related

No Login Windows For DB

Jan 19, 2007

Greetings,
One of the DBs on my mssql server should ask for a login and password to change content. The content/DB is access via web page. Any suggestions as to why it has suddenly stopped challenging the user for login? I checked the services and all the necessary ones seems to be running.:confused:

Thanks in advance.
spdunne

View 1 Replies View Related

Drop Windows Login

Jun 22, 2007

I've got a Windows login, which I cannot drop. I have checked about everything I can think of and can't find a connection to this user. Is there a script which anyone has which will list all object referencing this users? SQL 2005 ENT x64. It's a windows domain user.

View 1 Replies View Related

Linked Server And Group Windows NT

Nov 17, 2000

Hello, I do not succeed in authorizing the access of a group Windows NT to a linked server, on the other hand if I specify user's name it works.
Please inform me.
Pascal

View 1 Replies View Related

Sp_attach_db And Security With Windows Group

Nov 12, 2003

Hello,

We use local server windows groups to allow or disallow use of our SQL databases. We are now being asked to move off of our current server. My thoughts are to dettach and attach the database. What is going to happen to all my permissions based on the user groups of our old server? I intend to create the same group names on the new server but they will be newservergroupname as opposed to oldservergroupname. Is it just a case of scripting the database user, roles and object level permissions an manually changing oldserver to newserver?

View 3 Replies View Related

Default_Schema Clause Cannot Be Used With A Windows Group

Nov 6, 2007

Hi All,

I am getting error when I try to create a windows group and set default schema to db_owner.

Error message

Alter failed for user 'Domainuser'.(Microsoft.Sqlserver.SMO)

Additional information
An exception occured while executing a Transact-SQL statement or batch.
(Microsoft.Sqlserer.ConnectionInfo)

The DEFAULT_SCHEMA clause cannot be used wth a windows group or with principals mapped to
Certificates or asymmetric keys.(Microsoft SQL Server, Error:15259)

Please help.


Regards
Hassan

View 7 Replies View Related

Windows Group Authentication In SQL Server

May 23, 2008

Does anyone know how windows authentication works in SQL Server 2005 with regard to group membership? If a user is a member of two NT groups that have the same permissions in SQL then which group will be used to authenticate their login? The reason I ask is that logins have default languages associated with them so if a user is a member of NT group A with a default language of British English and is also a member of group B with a default language of US English then which one will be used and will it be the same one every time?

View 4 Replies View Related

SQL 2005 Login With Windows Accounts

Dec 19, 2006

Hello,
I'm having a problem using Windows Accounts to login to a SQL 2005 Server.
Here is my setup. The SQL server and web server are separate machines. I'm also not developing directly on the web server.
SQL Server - Windows 2003 Server- SQL 2005- Set to use SQL and Windows AuthenticationWeb Server- Windows 2003 Server- IIS 6.0   - Anonymous Authentication is disabled   - Integrated Windows Authentication is enabledApplication web.config:
<?xml version="1.0"?>
<configuration xmlns="http://schemas.microsoft.com/.NetConfiguration/v2.0"><appSettings>   <add key="ETR_Environment" value="Dev"/></appSettings>
<connectionStrings>   <add connectionString="Data Source=sql-dev-server, 1179;Initial Catalog=ENV_ETR;Integrated Security=SSPI;" name="ETR_Dev"/>   <add connectionString="" name="ETR_Prod"/></connectionStrings>
<system.web>   <compilation debug="true" strict="false" explicit="true"/>   <pages>      <namespaces>         <clear/>         <add namespace="System"/>         <add namespace="System.Collections"/>         <add namespace="System.Collections.Specialized"/>         <add namespace="System.Configuration"/>         <add namespace="System.Text"/>         <add namespace="System.Text.RegularExpressions"/>         <add namespace="System.Web"/>         <add namespace="System.Web.Caching"/>         <add namespace="System.Web.SessionState"/>         <add namespace="System.Web.Security"/>         <add namespace="System.Web.Profile"/>         <add namespace="System.Web.UI"/>         <add namespace="System.Web.UI.WebControls"/>         <add namespace="System.Web.UI.WebControls.WebParts"/>         <add namespace="System.Web.UI.HtmlControls"/>      </namespaces>   </pages>
  <authentication mode="Windows"></authentication>  <customErrors mode="Off"></customErrors>  <authorization>     <allow users="XXXWilliam.Klein"/>     <deny users="*"/>  </authorization></system.web></configuration>
The reason why I want to use the windows login to connect to the database is the application needs to keep track of who did what when entering and updating data but still keep them using there windows login accounts. So using a generic account will not work.
What keeps happening is I keep getting this error: Login failed for user 'NT AUTHORITYANONYMOUS LOGON'. When trying to connect the database. I've tried this on two web servers on another I get something slightly different: Login failed for user 'XXXWeb-Server$'.
Anybody able to give me any suggestions on how to fix this?

View 12 Replies View Related

Windows Authentication Login Permissions

Feb 18, 2002

Hi,

I have created a Windows user login and have granted it the appropriate roles (including a revoke).
As a test I also created a standard user and gave it the same roles as above.
When I run a query against the revoked table, the results are unexpected.
The Windows user can run the query, and the standard user gets a permissions error (which is what i expect).

Does anyone have any ideas as to what is happening here? I am still confused.... Does it have anything to do with the public permission that much be granted?

Thanks

View 1 Replies View Related

Creating Login In Sql 7 On Windows 2000

Sep 26, 2002

Hi

My platform is mainly windows nt4 server with two windows 2000 member servers. We recently acquired a higher spec machine to replace the old sql 7 server that is on windows NT4 box. The new server is a windows 2000 box and I have installed sql 7 on it.

The problem is when I try to create a new login it does not give me the option to select my domain. When I select new login and go for windows nt4 authentication the drop arrow for Domain only has the server name and not the domain name. If I manually input the domain name it rejects it.

I do not have Active Directory or a local dns server running on my network as we have no intention of migrating to Win2000 until next year. Our internet service provider handles our dns issues.

Is it not possible to install sql7 on win2k box.

Thanks
Eve

View 4 Replies View Related

Specify Alternate Windows Login Credentials

Apr 28, 2008

Is it possible for me to login using windows login information that is separate from my current windows login? That is to say, can I login to a SQL server using windows athentication but actually specify the login and domain information? I woul like to login to a SQL server from a computer that is not part of the domain using my windows credentials and not a SQL user name.

View 5 Replies View Related

How To Create A Windows Authentication Login

Jan 16, 2007

Is it possible to create a windows authentication login for each of the computers on my network that will connect to sql server. I am developing a program that will be installed on multiple computers and we do not want to actually have to log in to sql server since we will have to log in to the server before opening the program and we dont want to have 2 logins. I would like to use windows authentication so that I do not have to program multiple connection strings in my program so that if more than 1 person accesses the database at the same time it will not cause a problem with using the same login. Is this possible and if so how would I go about setting up the logins. I am using SQL Server 2005 and XP SP2

View 8 Replies View Related

Creating A Windows Athentication Login

May 3, 2006

Hi all. Here is my problem. Computer A has sql server 2005 express installed on it together with sql management studio. Computer B has only sql management studio. Computers are members of a corporate domain and are running xp professional. After starting management studio on computer B the databese engin on computer A is visible in the 'select server to connect to' dialog under network servers. When i try to connect i get the message login failed - so i try to add a windows athentication login for the appropriate user on computer A. The problem is as follows: Under 'Create new login/Select user or group/location' no network location apear and i can not add a network user name. This seem to be a network connectivity issue. I have, however, adjusted network connectivity via surface area configuration and enabled tcp/ip and named pipes - the server is visible form another computer as i have prviously mentioned.

View 1 Replies View Related

Login Windows Authentication Problem

Mar 17, 2006

Hi,

I have a problem with sql server 2005 express, when i try to connect to an sql database it says login: is not a trusted connection to an sql database. I have gone into reg edit and changed the authentication setting from windows oly mode 1 to mixed mode 2. It still has the same problem, does anyone have any idea what the problem may be?

kind regards

Chris J

View 1 Replies View Related

Login Failed From Windows Service

Dec 1, 2006

I'm building a Windows Service which keeps getting denied access to SQL Server 2k. The Service operates as a System User rather than an individual user profile.

I've tried Trusted_Connection=yes and Integrated Security=SSPI but these are both denied.

Any ideas what the connection string to connect to SQL Server 2k from a Wndows Service is?

View 4 Replies View Related

Group Login Members

Feb 20, 2008

Hi All,

'SACCAPRiskGroup' is my group login which has few users.
Now i want to know all users in this group. For this i am using
EXEC sp_helprolemember 'SACCAPRiskGroup'.

But no luck.

Can any one help me in this.

Thanks.
Malathi.

View 2 Replies View Related

Need To Identify Which Group The Windows User Is Logging In

Jun 18, 2007

Hi

In our sql server we have around 40 windows group.
Say a Windows user = "X"
This X user does not have a direct windows login, he is present in one or more windows groups registered in the sql server.

I need to know throught which group he is logging in.

If I know this , this will help in my auditing process.

Thanks in advance.

Regards
Soorya

View 1 Replies View Related

SQL 2012 :: Checking Windows User Group

Jun 7, 2015

I am setting up security for access of database tables for members in a specific Windows User Group.I want to check in a SQL script if this Windows User group is added and if so, add database users and grant SELECT on specific tables.

I have tried this:

SELECT * FROM master.sys.syslogins WHERE name like '%FoeUsers' AND isntgroup = '1'

but that selects a SQL user or group and not a Windows Group.Is there a way to check if a Windows NT (active Directory) user group exists?

View 5 Replies View Related

Granting Db_owner To The Windows Group In 2005

Feb 12, 2008

When granting db_owner to the SINGLE user, it's automatically set the DEFAULT_SCHEMA to dbo so when the user created the table, the table will be owned by "dbo"

When granting db_owner to the Window group, DEFAULT SCHEMA WILL NOT be "dbo", for this reason when the user A(belonging to this Windows Group) created a table, that's table will be owned by user A instead of dbo.

1. is it normal behavior for the Windows group?
2. Is there any way you can grant to the group so the objects will be owned by dbo instead of the user who created the objects? or this is a behavior when MS introduce SCHEMA in 2005.

Thanks,

View 1 Replies View Related

SP Execute Permission Denied To Windows Group

May 9, 2007

Hi

I'm fairly new to the world of SQL security (primarily a VB developer) and have a couple of questions/issues to resolve:
The error I'm getting in my app is "the execute permission was denied on the object 'MySP'...schema 'dbo'". In essence I cannot execute any stored procedures when logged on as a non-admin user (all this was of course fine in my development environment, now I'm hitting the security issues!).
My security is set up as follows:


Using Windows Authentication

I have an Active Directory Group "Group1", to which my non-admin user "User1" belongs.
In SQL Mgt Studio I created a login for Group1 under Security/Logins
I then created a user under MyDatabase/Security/Users, called "MyDbUser1". This uses the login above.
All SPs are owned by dboSo what am I missing?

I'm running SQL Express 2005 SP2 on W2003

many thanks

Richard

View 4 Replies View Related

SQL2000/2005 Logon Via Windows Group

May 30, 2007

I've come up against a wall with regards to adding login via a windows group. My end goal is to be able to manage sysadmin access to many servers via a domain group instead of via individual logins at each server.

I've got a mix of 2000 and 2005 servers, with a related issue for each. Searching far and wide has come up empty so far.

In each case below, the a login for the group was created via EM/SSMS, and set with the sysadmin role.

1. Domain Local Group

I added a domain local group "domainsql accounts" with myself among others as members, including the domain account for the sql and agent services.

Adding this group to SQL2005 worked fine, and authenticates my login as a member of this group. Oddly, xp_logininfo [domainsql accounts] , 'members' returns error code 0x8ac.
It does log me in, and it does correctly grant me sysadmin.

Adding this group to SQL2000 from EM, the group does not appear in the list for the domain. Typing it manually does recognize it as a group, but it does not allow login.

Review:
2000 fails login, fails xp_logininfo
2005 allows login, fails xp_logininfo

2. Domain Global Group

Next, I added a global group "domainsql dba", with myself among others as members.
This group IS viewable in SQL2000 EM to add as a login. Unfortunately, I still cannot login via windows auth. Even stranger, xp_logininfo [domainsql dba] , 'members' correctly returns the members of this group. I know SQL can authenticate that group, and I am part of that group, yet it won't authenticate my login?

SQL2005 fails to allow login but will also return member info via xp_logininfo.

Review:
2000 fails login, returns xp_logininfo
2005 fails login, returns xp_logininfo

The closest thing I found was a MS kb entry that suggested creating a local group, adding the domain group as a member of the local group, and adding a login for the local group. It didn't work.

Thanks for any offered suggestions

Chris

View 6 Replies View Related

Cannot Remotely Login To SQL Express Since Last Windows Update

Nov 17, 2006

Since today (after installing the latest Windows Updates !on my local machine!) I cannot login to a SQLExpress instance remotely; stating that it cannot find the server/instance.


The weird thing is: it only happens when I want to login to the non-default instance (sqlexpress 2005) (<IP><instance>).

When i'm using the default instance (msde 2000 or sqlexpress 2005 on another machine) (<IP>) it seems to work fine.


Does this have something to do with the windows updates, or is it just a coincedence?

Thanks in advance,

Remco Ros

View 4 Replies View Related

Permissions Not Effective For Windows Authentication Login

Jul 7, 2007

Hello All,



I'm hoping someone can help me with this puzzle.



Most logins I've created have been SQL Server authenticated. I assign the login newEmployee to a role existingRole, and ensure the role has the required permissions. This didn't seem to be rocket science....



My company has been provided with an application with a SQL Server back-end. My instructions were to create a Windows authenticated login and give it full access to the database. I followed the above principles, but running the application, the user got the error -



SELECT permission denied on object 'sysobjects', database 'databasename', owner 'dbo'.



So I decided to try the simplest possible scenario to make it work:



I've created a login DOMAINewEmployee with Windows authentication.

DOMAINewEmployee has been granted access to databasename.

By default, DOMAINewEmployee is a member of Public.

Public has been granted all available permissions on all objects.



ie... grant all on userTables to public

........grant all on sysobjects to public

........grant all on otherSystemTables to public

etc.





Running the application, the user still gets the above error. I'd send the problem back to the vendor, except if I've logged onto the PC as DOMAINewEmployee, querying -



select * from dbo.sysobjects



via Query Analyser produces the same error message. (An equivalent error message is produced when querying a user-created table).





To compare, I then created a login newEmployee2 with SQL Server authentication.

newEmployee2 has been granted access to databasename.



select * from dbo.sysobjects



runs successfully from Query Analyser (as to any queries on user-created tables).





What else is required to grant access to tables from a Windows authenticated login?







( What really scares me, is that the application will run if I make the Windows authenticated login a member of server roles System Administrator and Database Creators, then the application will run - but I don't want this to be the permanent solution. Even after doing this, the above query still fails in Query Analyser for that login, suggesting that there is something wrong with how I configured the permissions. )





Any help would be appreciated.





Thanks.



Kim.

View 4 Replies View Related







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