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
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?
If I have a domain user DOMAINuser1 who belongs to multiple window groups say DOMAINLookupConfigUsers and DOMAINAuditConfigUsers. In sqlserver, I would create two logins - DOMAINLookupConfigUsers and DOMAINAuditConfigUsers and matching users in the database. Then I grant LookupConfig role to the LookupConfigUsers user and AuditConfig role to the AuditConfigUsers user in the database. When DOMAINuser1 logs in, will it have both roles? I try to set this up but it does not seem to work. The domain user only picks up one of the role. Am I on the right track? If not, what is the proper way to grant multiple roles to a user when it belongs to multiple groups and each group has different privileges in the database.
View 4 Replies View RelatedI 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?
== I asked this question directly to Remus and wanted to share the response to all of those people using this forum ==
We recently moved our database server from SQL Server 2000 to SQL Server 2005. All applications on our intranet development server stay the same [VS.NET 2003], but recently resources in our Dev DB server ran out of space. While doing a thorough investigation, I noticed ERRORLOG file was occupying about 35 Gig of HDD space. I immediately checked SQL Server error log and noticed an entry which says €“
===========================================================================================
Date 7/7/2006 4:45:37 PM
Log SQL Server (Current - 7/7/2006 4:45:00 PM)
Source spid77s
Message
The activated proc [dbo].[SqlQueryNotificationStoredProcedure-5eaf8465-d0cb-4be7-93b6-44bb979dd41c] running on queue BW_Content.dbo.SqlQueryNotificationService-5eaf8465-d0cb-4be7-93b6-44bb979dd41c output the following: 'Could not obtain information about Windows NT group/user 'BWCINCHoffK', error code 0x534.'
===========================================================================================
What is this SqlQueryNotificationService in my database? Is it a SQL Server 2005 thing? Why the same kind of stored procedure does not exist in other databases, but BW_Content? This error is getting repeated most probably every second and is filling up our server.
I believe our corporate IT people removed our domain accounts from BWCINC domain to BWCORP domain and probably some application which is using BWCINCHoffK credential is getting errored out. I tried to locate this application and was not successful.
Is there anyway that I can stop this ERRORLOG from growing? How can I delete these log entries so that I can make space on our Hard Drive? Is there an easy way in SQL Server 2005 to locate which application is creating this error?
Response from Remus:
The 'SqlQueryNotificationService-...' is the service created by SqlDependency when you call SqlDependency.Start (). The problem you describe appears because the 'dbo' user of the database is mapped to the login that originally created this database. The SqlDependency created queue has an EXECUTE AS OWNER clause, owner is 'dbo' and therefore this is equivalent to an EXECUTE AS USER = 'dbo'. The error you see is reported by the domain controller when asked to give information about the original account 'dbo' mapps to (that is, BWCINCHoffK'): Error code: (Win32) 0x534 (1332) - No mapping between account names and security IDs was done.
To solve the issue, change 'dbo' to match a correct login, using either sp_changedbowner or ALTER AUTHORIZATION ON DATABASE::[dbname] TO [somavalidlogin]
To find the databases that have this problem, run this query:
select name, suser_sname(owner_sid) from sys.databases
The databses that have the problem will show NULL on the second column.
A similar problem is described here: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=65711&SiteID=1
To remove the entries, use sp_cycle_errorlog to force a new errorlog file, then delete the huge log file.
---------------------------------------
I executed ALTER AUTHORIZATION ON DATABASE::[BW_Content] TO [sa];
I got this error in SQL Error Log once and the growth of ERRORLOG was stopped.
===============================================================
Date 7/10/2006 1:16:55 PM
Log SQL Server (Current - 7/10/2006 1:17:00 PM)
Source spid20s
Message
The query notification dialog on conversation handle '{6BDE95F7-0EFB-DA11-9064-000C2921B41B}.' closed due to the following error: '<?xml version="1.0"?><Error xmlns="http://schemas.microsoft.com/SQL/ServiceBroker/Error"><Code>-8490</Code><Description>Cannot find the remote service 'SqlQueryNotificationService-c15bb868-ed56-47d2-bf91-ce18b320989a' because it does not exist.</Description></Error>'.
===============================================================
Should I be concerned about this error?
Thanks
-Binoy
How to identify different fields with in a group of records?
Example:
create table #test
(ID int, Text varchar(10))
insert into #test
select 1, 'ab'
union all
select 1, 'ab'
[Code] ...
I want to show additional field as Matched as ID 1 has same Text field on both the records, and for the ID 2 I want to show Unmatched as the Text fields are different but with the same ID.
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
How can you tell which datatypes in a given database are user defined(with a query, not by looking in Enterprise Manager)? This is for SQLServer 2000.
View 2 Replies View RelatedWhen using Dtexec to run a package, is there a way to prevent/suppress the windows event log information messages:
- Package "Xyz" started.
- Package "Xyz" finished successfully.
We are starting to work with SSIS in our production environment & due to support issues; we are trying to get rid of the "Package xxx started" log entries inside of the Windows Application Event Log...
So far, I have tried many different things, including setting the LoggingMode to "Disabled", as well as adding a new logging reference with a different destination... All of which still do not get rid of the extra log entries...
how to get this done?
Good morning,
View 7 Replies View RelatedHey all,
I've got a third party app running atop my sql server instance. When auditing logons in profiler, I see that the user (sa) logs off, and then immediately logs back on. Interestingly, the app logs back on with this user again, and the instance gives it the same spid as it previously had.
Can anyone explain this behaviour, or point me to a place where I can investigate ?
Thanks,
-Kilka
I have a tables that looks like this:
TABLE1
COL1
COL2
LOG_TABLE
TABLE_NAME
USER_ID
SQL_STATEMENT
DATETIME
I need a trigger (I assume) to log any updates/inserts/truncate/deletes/etc. to any changes as well as their USER_ID that has been executed to TABLE1.
We are looking at developing an SQL Server 2005 Database and I would like to use Windows Authentication rather than SQL Server Authentication to connect our client app.
In our development environment, we have two Servers, one being used as a file server and the other as an SQL Server. We have now set up a domain using the file server as the domain controller. (We had previously been set up to use a workgroup).
I have set up an active directory group called SqlDevelopers and added an active directory user called Jonathan to it.
On the SQL Sever, in management studio, I have set up a new server login which uses windows authentication called DomainSqlDevelopers. I used the GUI to verify I could see the domain and the group.
The default database is set to a test database on the server. A user in the test database is mapped to the DomainSqlDevelopers and given the Roles dbo, db_datareader, db_datawriter.
To test the log in, on the server, I logged out as administrator and in as Jonathan. I could successfully access the server through management studio using windows authentication.
However, if I log in as Jonathan on my client PC and try to access the SQL Server using management studio and windows authentication, I have problems.
The first time I try I will get a timeout error. If I try again will get either:
Login failed for user ''. The user is not associated with a trusted SQL Server connection
Or
Cannot generate SSPI context
I can€™t determine any pattern to which of the above errors I get.
However, if I log in as administrator on my client PC, I can connect to the server using management studio and windows authentication.
Sounds like Active Directory/Domain or other Network issue (Not really my area). I would be grateful for any help.
Thanks,
Jon
I have a user who cannot login to a .mdb when he is logged
in to Windows, on his workstation or any other.
No one can login to the .mdb when he is logged in to Windows.
He CAN login to the same .mdb if someone else is logged in
to Windows, either on his workstation or any other.
When the user logs in he gets the following error:
MS SQL Server Login
Connection Failed:
SQL
State: €˜28000€™
SQL Server Error 18456
[Microsoft][ODBC SQL Server Driver][SQL Server] Login failed
for user €˜domainusername€™
I have removed and recreated the ODBC Connection
while he is logged in. I have tried changing the trusted login to
administrator (in the window that pops up when he fails to connect).
He has the permissions he needs to the .mdb because he can
login under anyone else€™s profile.
I tried copying his profile to a brand new user and ended up
with the same result.
This leads me to wonder if there is some corruption in one
of his profile€™s .dat files or ???
This is the boss€™ account and he does not want to be given a
new username and profile€¦
Microsoft SQL Server
2000 - 8.00.760 (Intel X86)
Running on MS Windows 2003 Server Standard SP1
All workstations are XP SP2 with MS Access 2003 SP2 (from
Office Pro distro)
We use roaming profiles.
All systems within the same physical and logical network and domain.
Any help you can provide will be greatly appreciated.
Please let me know if there is any additional information you need.
Thanks,
Mike
I want to establish logging using NT Security. The problem is that user names in NT have underscore(_). When I establish NT Scurity in SQL security manager, SQL Server tries to add blank names as login id and they will not go through. Is it manadatory to remove underscore from username?
Because of structrual hierarchy, I have different groups in NT to be mapped to SQL Server groups and each group has different permission on SQL tables. In some cases we might have same users in more than one group. I read that it is not possible if you use NT integrated security. Is there any thing that can be worked around this and is this problem still exists in SQL 7.
Thank you in advance
Ali Malekshahi
Hi,
I am using SQL Server Express Edition 2005 as a backend database working with Visual Basic 2005.
I am using Vista and having two users to access to my computer. User-1 and User-2.
I created a database in User-1 and works fine Visual Basic 2005.
Now the problem is when i login to my computer system with User-2. I cannot able to access the database with encountering error like "User-1/SQLExpress". I know that i cannot able to access to database which was created in User-1.
Do you any solution to this problem. when i login with user-1 and user-2 it should able to access database.
Thanks.
Regards
Kashif Chotu
Greetings,VWD EE and other Tools do not have problems working O.K. on my machine, when I am logged-on as restricted(limited) user, both environment and local web server are functioning, and it was MS effort to do it right.BUT the Problem is, when I try to connect to database->new DB connection(either through vwd or management studio etc). I get an Error - which should be written to event log (according to msg) but it isn't - that I can't connect. I am sure that it is because NO SQL SERVER2005EXPRESS instance is running, which I think is the only prerequisite to have it work (I do not need sqlbrowser service I am doint local development).SQL2005express service is configured on MANUAL start. I can start it as administrator through SQL config manager, but it is not convienient and what I want. I need to start it ONLY as a developer user, I do not want it to be running all-the-time for everyuser using computer. It was congigured as Network Service logon, I tried Local Service logon, and I even tried configuring it to logon as -my developer user- account, e.g. with limited user name and his password.In every case I can't start the service as member of users group and this developer. Then I added limited user to SQL2005EXPRESS group. Still NO help, won't start either.My question is, If I want to start developing ADO2.NET application and I need to have running SQL2005Express instance as a develper, how can I start it? I think I do not have some rights to masterDB or something. Do it allways need to be running when computer starts? Isn't there any other way, to start it JUST when I think I start developing?I know of user-mode of accessing SQL2005express DBs, but it also assumes that SQL2005Express service is already running, what I am trying to prevent. I do not want to have it running for everyone who uses computer, just for someone who neeeds it.Any help explaining me the right way HOW TO SETUP WORKING ENVIRONMENT when developing as limited user welcome, I read the the documentations and haven't found the answers.THANK YOU !
View 1 Replies View RelatedI would like to fire a pre execution event, grab the name of the stored procedure (source of the sql task), insert a record with the name and datetime, and then fire a post event that would update the record with a modified dated.
What is the best way to capture the source value name in the execute sql task.
Hi all,
I have a SQL Server 2005 Express edition instance set up on one server, and IIS on another server.
The SQL Server process account is a domain user account, which I have added to the local groups that SQL Server created during installation (I originally used a local user account instead of domain account; however, the problem occurs with both).
SQL Server runs fine, and if I set my IIS application pool identity to a domain admin, my web app can access the database and retrieve the data necessary.
However, I have a domain user account that I want to use to run the app pool and retrieve the data. The domain user account is added to the IIS_WPG group on the web server. On the database server, I have created a login for the account, as well as added it to the db_datareader role of the database that is used for the site.
However, the user is not able to connect to the SQL Server. I get the "Login failed for user <user account>" error in ASP.NET. I also tried connecting with SQL Server Management Studio, and I get the same error. I checked and the user has connect permission to the database server.
With admin accounts, there are no problems logging in, etc.
Any pointers are appreciated,
Thanks,
SA.
Edit: I was able to find out that the State is 11 for the error. According to http://blogs.msdn.com/sql_protocols/archive/2006/02/21/536201.aspx, this indicates "Valid login but server access failure." I am not sure how to resolve this.
We were using sql authentication on our sql server 2000 servers, Butneed to use windows authentication. I have an sql user jdoe that ownsmany different objects in multiple databases. I have tried adding thewindows login of jdoe with the exact same permissions as the sql userjdoe, but it complains because it says jdoe already exists. So Iattempt to delete the sql user of jdoe (remembering the permissions soI can reapply them to the windows user soon to be created) But it says"you cannot drop the selected login id because that login id ownsobjects in one or more databases" So now I am trying to figure out howto change the ownership of those many many objects in the manydatabases from jdoe to dbo so I can delete this sql user of jdoe. Doesanyone know of a way to convert an sql login to a windows login? Or ifthat can't be done, how to change the owner of many objects to dbo so Ican delete this user? Maybe a T-SQL script or stored procedure. Thereis a stored procedure called sp_changeobjectowner but that only worksfor one object, how would I do that on all the objects that jdoe owns?As you can see I am a newbie but I appreciate any help you canprovide!!! Thanks in advance!
View 3 Replies View RelatedHello, 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
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?
Hello,I am putting together a row level security plan for our salesdatabase. I will give a brief description of the method I am thinkingof using to give you an idea of how I will need to be able to discoverthe 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 thebranches of our company. Each order(row) that is inserted has an entryin the division_number column to describe which branch the orderbelongs to. I have created another table(Branch_Folks) that has fourcolumns; username, windows_group, SQL_Login and division_number.I am using a view and SQL logins to control access to the data basedon the user, the SQL logins give windows groups access to the view.Only users that are added to the specific branch groups will haveaccess to the logins, but if a user is added to the group without mebeing notified, then he will not have a corresponding entry in theBranch_Folks table. Currently I am using the SUSER_SNAME() function todetermine which user is accessing the data from the view that I havecreated. The view uses this select statement to filter the data basedon the user & division.Select * from tsalesfact A, Branch_Folks B where SUSER_SNAME() =B.username and A.division_number = b.division_numberThis method works fine, but I will have to manually maintain the userlist in the Branch_Folks table in case a new user joins the windowsbranch 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 View RelatedHi 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
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?
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,
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
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.
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
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
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
Greetings,
I am developing a package on my local workstation. I have defined two logging service providers. One is for SQL Server and the other is for the Windows Event Log. I am using the Dts.Log method in a script task to write log entries.
Logging is working properly with the SQL Server provider and rows are being inserted into the sysdtslog90 table. However, the only events that are being logged in the Windows Event Log are the package start and end events which I believe SSIS is doing automatically anyway.
Is there something I need to do to enable WIndows Event Log logging other than defining a log provider and making sure it is checked active? Won't SSIS write to two different logs with one Dts.Log call? Any ideas on what might be going wrong with my approach?
Thanks,
BCB