Windows Groups For SQL Server - What Am I Doing Wrong?
Jul 23, 2005
Hi there,
I have an Access front end application that uses a SQL Server 2000
Desktop Edition backend. I have created a package with the Access
2003 runtime that I'd now like to deploy. I have created a global
group in the domain and placed the users that I'd like to give access
to the database (via my application front end) in that group. I've
then created a local group on the SQL Server machine and added my
global SQL Server group to it. I've also added that local group as
database user from SQL Server. From my reading of the various
material on the Internet this should work but it doesn't. I can't
access the database via the application from any machine other than my
development machine. My VBA code specifies integrated security for
the connection string.
Help please! I'm so close to deploying this and now I'm bogged down
in security. Any ideas anyone has would be appreciated. Account
administration and Windows security is not my strong suit.
Barb
View 2 Replies
ADVERTISEMENT
Oct 19, 2005
Those of you who have installed SQL Server 2005 may have noticed that the installation creates several new Windows groups on the server. Do not underestimate the importance of these groups.
View 3 Replies
View Related
May 16, 2008
Hi,
We have just created a new Windows 2008 server running SQL 2005 64 bit. I am in the process of migrating old SQL 2000 databases onto this new server. One of the databases required a SQL login for some Windows groups that get created from the SMS software. I'm having a problem when I go into create a new login, select object type Groups, from this location , I enter the object name and click Check Names and it finds it. I select okay and everything looks good. But when I click ok on the New Login screen, I get the 15401 error saying that Windows NT user or group not found. I have gone into the Server Manager - Groups and verified that the group exists, and it does. The only thing I see when I click properties is on the bottom right had side of the screen it says "Changes to a user's group membership are not effective until the next time the user logs on." It says this for all the groups on this server and I get the same error message when I try to add any group. I have had the user that is a memeber of this group log off and back on a few times, but it still says the same thing and I still get the same error.
Anyone have any ideas on what the problem might be? Is there something on the Windows 2008 server setup that we missed?
Thanks,
Isabelle
View 1 Replies
View Related
Oct 11, 2002
Hi, i had installed on a lan, sql server 2000 (administrative and client tools) on workstations with windows nt4 ws.
The problem is that i shouldnīt give users permissions of "local administrator".
If I give them permissions of only "local users" they canīt start sql services, but they can start the enterprise manager.
If I give them permissions of "advanced users" they can start sql services, but they canīt start the enterprise manager.
What can i do for this to work well, without give them manager rights?
Thank you.
View 7 Replies
View Related
Jan 9, 2008
I want to map users/groups of a .NET application which uses MS SQL Server to Windows users/groups. Then grant pemission to the Windows users/groups to access the MS SQL Server.
If possible, I want to list Windows Users and Groups regardless of whether Active Directory is used or not.
View 1 Replies
View Related
Nov 8, 2006
Hi
We are planning implementation of a currently Sybase db. The users (about 3600) will be i 5 domains and we want single sign-on through trusted connections. We want to use the database roles to define different user access on databases and tables. There will be around 2000 roles. We also want to add the users directly to the database roles without having to grant each user database access.
So I thought that I could add the user groups from all domains and then add each domain user account to specified database roles. Am I right here or what? The Windows authentication will lookup or check the users kerberos ticket during logon process and allow logon.
The documentation here is weak and I assume it's a windows authentication question but wondered if any of you guys had been down the same road.
For creating the groups I have the following options:
Create a domain group and put all the usergroups from the other domains in this group
Add user groups from all other domains directly into the SQL Server.
Any recommendations here?
View 3 Replies
View Related
Aug 29, 2006
I created a Windows Group in Active Directory ("Database1Users"). populated it with users, and planned to allow everyone in it to have access to a sql 2005 database.
I went to the sql server, Security (at the general level), Logins, New login, and created the Login "<domain name>Database1Users". I assigned "Database1" as Default database, selected the database and assigned it to the above user name (which is actually a group name). I also typed in a default schema of "dbo" and gave the user account the role "db_owner" (just learning....) . Pressing OK gave me this error message:
>>>>
The DEFAULT_SCHEMA clause can not be used with a Windows Group or with principals mapped to certificates or an asymmetric keys."
>>>>
Oh..... how am I supposed to map a Windows group to give the users the access they need?
TIA,
barkingdog
View 1 Replies
View Related
Aug 24, 2007
I am having a problem with permissions using Windows groups. I have a database (database1) that has permissions granted via Windows groups. Two groups (group1 and group2) are members of the db_datareader role in database1, and this work fine. Do to the number of tables that get created during our work, using db_datareader is the easiest way to keep up with permissions without creating a maintenance problem. Now I have a table that I want to add to this database, but I only want group2 to have select permission on this one table which is a problem because group1 has the db_datareader role. So I thought I could create a view in this database to the restricted table that I put in database2. Then in database2 I only added group2 as a user with the permission to select from this table. Unfortunately the group membership does not seem to get interpretted correctly in database2 and no one can successfult select from the view in database1.
In other words, user1 who belongs to group1 connects to database1 and cannot select from the restricted view -- this is what I would expect. However, when user2 who belongs to group2 connects to database1 they also cannot select from the restricted view -- not the behvior I would expect. Now, if I make user2 a user in database2 with select on the restricted table then user2 can connect to database1 and successfuly get data from the restricted view. So it looks like the fact that user2 belongs to group2 is never passed to database2 via the select from the view on database1. Is this indeed the way that Windows group security is working or is meant to work in SQL Server?
I realize I could solve this simplified version of the problem by creating my own role in database1 for group1 etc., but I am trying to solve a bigger problem in our environment that has hundreds of databases across numerous servers.
Thanks
Rob
View 4 Replies
View Related
May 6, 2007
I'm developing an ASP.NET2.0 application which accesses a SQL Server 2005 Express database. I plan to use integrated security for access to the database.
I'm confused about the relationships between Windows groups, the ASP.NET web.config file <allow roles=.../> and SQL Server roles.
I would like to create a Windows group to which I can assign multiple users and grant that group access to a Web Site using windows authentication and also grant that windows group access to the database my web application uses.
I have gotten the combination of Windows Authentication to the web site and to the database to work for a specific windows user but I am having trouble determining the combination of database security entities I must create to allow access to my database by members of the windows group.
For a Windows user:
1. Create Windows user
In SQL Express
2. CREATE LOGIN FROM WINDOWS WITH DEFAULT_DATABASE =
3. CREATE USER FOR LOGIN
4. CREATE ROLE
5. EXEC sp_addrolemember <role-name> <user-name>
For a Windows group, what would be the equivalent commands necessary to grant a windows group access to my database? Specifying the Windows Group name in sp_addrolemember does not appear to be sufficient even though the documentation states that a windows group name is a valid value for the member name argument.
View 3 Replies
View Related
Dec 17, 2007
Hi there,I have a strange problem with an application running on Windows 2000connecting to a SQL Server 2000 with Greek collation.Runnin this query on the SQL"SELECT serverproperty('Collation'),databasepropertyex('mydb', 'Collation')"gives me:SQL_Latin1_General_CP1_CI_AS, Greek_CI_AIWhen I run my application on my Windows XP machine I have no problemswith the characters however when I try the same applicaton on aWindows 2000 platform the Greek characters are lost. For example theGreek characters TI appear as OE.Any help is appreciated,regardsBOB
View 2 Replies
View Related
Feb 19, 2006
Im trying to insert a record in my sql server 2005 express database.The following function tries that and without an error returns true.However, no data is inserted into the database...Im not sure whether my insert statement is correct: I saw other example with syntax: insert into table values(@value1,@value2)....so not sure about thatAlso, I havent defined the parameter type (eg varchar) but I reckoned that could not make the difference....Here's my code: Function CreateNewUser(ByVal UserName As String, ByVal Password As String, _ ByVal Email As String, ByVal Gender As Integer, _ ByVal FirstName As String, ByVal LastName As String, _ ByVal CellPhone As String, ByVal Street As String, _ ByVal StreetNumber As String, ByVal StreetAddon As String, _ ByVal Zipcode As String, ByVal City As String, _ ByVal Organization As String _ ) As Boolean 'returns true with success, false with failure Dim MyConnection As SqlConnection = GetConnection() Dim bResult As Boolean Dim MyCommand As New SqlCommand("INSERT INTO tblUsers(UserName,Password,Email,Gender,FirstName,LastName,CellPhone,Street,StreetNumber,StreetAddon,Zipcode,City,Organization) VALUES(@UserName,@Password,@Email,@Gender,@FirstName,@LastName,@CellPhone,@Street,@StreetNumber,@StreetAddon,@Zipcode,@City,@Organization)", MyConnection) MyCommand.Parameters.Add(New SqlParameter("@UserName", SqlDbType.NChar, UserName)) MyCommand.Parameters.Add(New SqlParameter("@Password", Password)) MyCommand.Parameters.Add(New SqlParameter("@Email", Email)) MyCommand.Parameters.Add(New SqlParameter("@Gender", Gender)) MyCommand.Parameters.Add(New SqlParameter("@FirstName", FirstName)) MyCommand.Parameters.Add(New SqlParameter("@LastName", LastName)) MyCommand.Parameters.Add(New SqlParameter("@CellPhone", CellPhone)) MyCommand.Parameters.Add(New SqlParameter("@Street", Street)) MyCommand.Parameters.Add(New SqlParameter("@StreetNumber", StreetNumber)) MyCommand.Parameters.Add(New SqlParameter("@StreetAddon", StreetAddon)) MyCommand.Parameters.Add(New SqlParameter("@Zipcode", Zipcode)) MyCommand.Parameters.Add(New SqlParameter("@City", City)) MyCommand.Parameters.Add(New SqlParameter("@Organization", Organization)) Try MyConnection.Open() MyCommand.ExecuteNonQuery() bResult = True Catch ex As Exception bResult = False Finally MyConnection.Close() End Try Return bResult End FunctionThanks!
View 1 Replies
View Related
Jan 25, 2008
I have a new SQL 2005 (SP2) Reporting Services server to which I've just upgraded and deployed some SSRS 2000 reports.
I have a subreport that contains a matrix with two groups. The report data seems to be inexplicably repeating the data for the first row in the group for all rows in the group. Example:
ID1
ID2
DisplayData
1
1
A
1
2
B
1
3
C
2
1
A
2
2
B
2
3
C
Parent group is on ID1, child group is on ID2, report would show:
1
1
A
2
A
3
A
2
1
A
2
A
3
A
Is this a matrix bug in 2005 SP2, or do I need to do something differently? I can no longer pull a comparison version from an SSRS 2000 server to verify, but I believe it was working as expected before...
View 2 Replies
View Related
Dec 12, 2007
Is it Possible adding Windows (activi directry users) from SQL Server which is running Windows authotication.?
View 4 Replies
View Related
Mar 26, 2008
Hallo there,
I just upgraded from Windows XP Pro to Windows Vista Bussiness and tried to reinstall SQL Server 2005 Developers Edition. After the installation i downloaded (using microsoft windows update) and installed all the service packs for sql and vista available.
My problem is when i open sql server management studio and try to connect to my default instance using windows authentication and database engine, an 18456 error occurs.
I enabled all the protocols and all the ports
I disabled windows firewall and antivirus (eset nod32)
I installed all service packs available
I have also installed Visual Studio 2005 without installing sqlexpress
But nothing happens!
Please i am very desperate, any information will be gratefully accepted.
This is my installation Information
Code Snippet
Microsoft SQL Server Management Studio 9.00.3042.00
Microsoft Analysis Services Client Tools 2005.090.3042.00
Microsoft Data Access Components (MDAC) 6.0.6001.18000 (longhorn_rtm.080118-1840)
Microsoft MSXML 3.0 5.0 6.0
Microsoft Internet Explorer 7.0.6001.18000
Microsoft .NET Framework 2.0.50727.1434
Operating System 6.0.6001
Thank you in advance,
Patonios
View 3 Replies
View Related
Apr 17, 2008
Does any one come across any difficulty in the following migration?
OS: Wondows Server 2K
DB: SQL Server 2K Standard Edition
Migrating to
OS: Wondows Server 2K3
DB: SQL Server 2K Standard Edition
Thank you,
Gish
View 4 Replies
View Related
Feb 7, 2007
Is it possible that i can create a SQL Server Group in sql 2005. Eg. in 2K when u right click (in Enterprise manager) on Microsoft Sql servers you can see "New Sql server Group". I am talking about this group. I have many servers in NYC and Dallas and in VA. I want to group them in the group names NYC DAL VA. is it possible.
View 12 Replies
View Related
Nov 25, 2014
I want two write a small script to determine which is the currently active (primary) server in the AG.
Right now, I see that using SELECT * FROM SYS.dm_hadr_availability_replica_states I can determine the role. However, when the server goes down and switches to the secondary node, I don't believe that the role changes (or does it?). How do I determine which is the active node?
View 9 Replies
View Related
Sep 29, 1999
In SQL 6.5 enterprise mgr I Could easily delete by right clicking on a server or group. In 7
this functionality is gone. Anybody done this.
Thanks
View 1 Replies
View Related
Oct 8, 2004
Hi.
Have problems connecting to SQL server with NT group authentication.
Get error 4064: Error connecting to user default database.
There's no problems connecting with NT users og SQL users.
SQL2000 + Windows2000 both running latest service packs. All SP have been reinstalled.
The server suddenly stopped accepting NT group logins. It have been running for 1 year with no problems - 4 days ago it started generating this error.
Any ideas anyone?
View 1 Replies
View Related
Sep 1, 2006
Please excuse me if this is basic SQL, however I'm new to SQL and how
it works.
I'm running SQL Server 2005 and have a number of databases. What I want
to do is control access to each database via an Active Directory group.
The group has been setup in AD and users added. I've then gone into SQL
Management Studio and gone to the Security folder under the server and
then Logins and added the group. Within that I've then added
permissions to a specific database. All good so far, however when you
try to login to the database I get a Login failed error message. In
contrast if I add individual users to the security, Logins on SQL
Manager I can get the DB to open with a username and password box for
the specific user.
What I would like to know is:
How can I control access to SQL Databases using Active Directory
Security Groups?
What setup is required on the SQL side to allow access to the databases
from users in the security groups, but still require them to login with
their windows authentication?
View 1 Replies
View Related
Feb 12, 2004
Hope I'm not going to annoy anyone here too much but I'm looking for a place to post a job opening and was hoping that someone out there might know of a good site or two.
The job will be in the north Denver area.
Thanks! (And apologies if this is completely inappropriate behavior.)
View 4 Replies
View Related
Jan 21, 2014
I have a dataset that contains an EmployeeID, StartDate, EndDate, and Location. Each record tells me at which location and employee was during a payroll period (fortnightly). So the data looks like:
EMP_KEYSTART_DTEND_DTLOCATION
120130117201301318103
120130117201301318103
120130131201302143354
120130131201302148103
220130117201301311234
220130131201302144567
120130214201302283354
220130214201302281234
Employees can be at multiple locations during the two weeks. They can work at one location, stop working there, start working somewhere else, and then maybe go back to their old location. There are duplicate records here as each employee can be at the same location multiple times during the two week period. What I need to capture is the actual start and end date of an employee at each location for each 'assignment'. An assignment is defined as a continual period of employment at a location with gaps of no less than 4 days - if there is a gap of more than four days then that is classed as a new assignment.
View 7 Replies
View Related
Mar 7, 2015
I am still fairly new to Sql Server 2008 R2 (express) , but am enjoying exploring it.
I have several databases up and running and now a large number of users in each.
But I notice whenever I write a stored procedure I have to go in and add each user to give them permission to exec it. When the user numbers are low its not a problem but I now have over 20 users on 1 database and its becoming tiresome.
I have heard of Active Directory on Windows Server but my database is installed on windows xp professional PC. Its running well and we don't have a budget to change it.
I've been searching the internet to see if there is some way I can create a group within sql server give the group all the necessary permissions/ privileges and then add the users to that group thereafter...
View 6 Replies
View Related
Mar 19, 2008
Hi all,
just want to confirm this:
We have a department and want to grant them access to various databases on a sql server 2000 instance. Can we create an AD group for these users and just add the group as a login to SQL Server? Or do we have to create a db user for each member of the group and map the logins one by one?
Thanks
View 1 Replies
View Related
Aug 6, 2014
I'm trying to do the following and haven't been able to figure it out.
Say there's a table with these records:
Col1 Col2 Col3
a b c
a b c
a b d
e f g
e f g
I want to generate a number that represents the groups of columns like this:
Col1 Col2 Col3 MyNumber
a b c 1
a b c 1
a b d 2
e f g 3
e f g 3
So that each grouping gets its own identifier. I've tried this:
SELECT Col1, Col2, Col3
row_number() OVER (PARTITION BY Col1, Col2, Col3
ORDER BY Col1, Col2, Col3) AS MyNumber
FROM MyTable
But I get this:
Col1 Col2 Col3 MyNumber
a b c 1
a b c 2
a b d 1
e f g 1
e f g 2
View 9 Replies
View Related
Jan 16, 2015
I have some data where a bit value changes over time and I want to rank it by the repeating groups, how do I write the SQL so that I get the result below?
I want to sort by create date, and every time the bit changes value we start the rank over.
If I partition by the bit field, it will just group the entire data set by the bit field.
Bit CreateDate Rank
1 3/15/2014 1
1 3/14/2014 2
0 3/9/2014 1
0 3/8/2014 2
0 3/6/2014 3
1 3/4/2014 1
0 2/28/2014 1
0 2/20/2014 2
0 2/15/2014 3
0 2/10/2014 4
View 3 Replies
View Related
Apr 2, 2015
The MSDN doc makes it sound like after a failover of the primary, the CDC data won't "keep working" on the secondary unless you "To allow the logreader to proceed further and still have disaster recovery capacity, remove the original primary replica from the availability group using ALTER AVAILABITY GROUP <group_name> REMOVE REPLICA. Then add a new secondary replica to the availability group."
We have a few CDC tracked tables that we use and the general idea of AlwaysOn I thought was to minimize all the overhead and let things "just work" so your apps just connect and the listener re-routes everything where it needs to go.
It looks like to get this working properly an automated job /trigger would have to wait for a failover event and then kick off tasks to remove and re-add the replica and perhaps start up the CDC job on the secondary?
View 1 Replies
View Related
Sep 15, 2015
I have a below table as:
IF OBJECT_ID('tempdb..#Test') IS NOt NULL
DROP TABLe #Test
--===== Create the test table with
create table #Test([Year] float,
Age Int,
)
INSERT INTO #Test
([Year], Age)
[Code]...
I queried below to get additional column
Select *,row_number() over(partition by [Year] order by Age) as RN from #Test as
YearAgeRN
2014301
2014312
2014323
2015251
2015262
2015273
2015284
2015295
i want one more addtional column (Desired Output) with max of RN in each group as below"
YearAgeRNDesired output
20152515
20152625
20152735
20152845
20152955
20143013
20143123
20143233
View 7 Replies
View Related
Oct 19, 2015
is there a way to restore all file groups except one? example: Database A has 10 filegroups, but 1 of them is defunct, so i cant delete it and there's no backup for restore it.Can I create a new DB restoring the 9 good FGs from a database A's backup?
View 9 Replies
View Related
May 24, 2006
I mapped a login created with an Active Directory Group on server A to a login on server B through a linked server on server A and received a null login error when attempting to connect.
I changed the Active Directory Group login to an individual active directory login and the connection worked fine.
I saw someone post online somewhere that Active Directory Groups don't work with linked server by design--but I wanted to get confirmation on this. Can anyone confirm this, particularly someone from Microsoft?
View 1 Replies
View Related
Dec 17, 2007
I have a clustered server environment where three of the SQL Server local groups were not created during installation. One of these missing groups is SQLServer2005SQLAgentUser$ComputerName$InstanceName, and this is causing problems with trying to automate replication.
How do I create this group after I have installed SQL Server?
Thanks,
Neal
View 4 Replies
View Related
Sep 19, 2007
It appears that I have a machine where the SQL Server 2005 Security groups were deleted, now I am unable to change account settings in configuration manager ( I get a WMI provider error ). Is there any way to recreate these security groups without uninstalling and reinstalling SQL Server?
View 3 Replies
View Related
May 21, 2014
how does security works between availability groups.
ex if i create an object and grant permissions to a user will that be replicated to secondary replica .
View 4 Replies
View Related
