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 RepliesI 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...
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?
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
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?
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?
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.)
Hi there,I have an Access front end application that uses a SQL Server 2000Desktop Edition backend. I have created a package with the Access2003 runtime that I'd now like to deploy. I have created a globalgroup in the domain and placed the users that I'd like to give accessto the database (via my application front end) in that group. I'vethen created a local group on the SQL Server machine and added myglobal SQL Server group to it. I've also added that local group asdatabase user from SQL Server. From my reading of the variousmaterial on the Internet this should work but it doesn't. I can'taccess the database via the application from any machine other than mydevelopment machine. My VBA code specifies integrated security forthe connection string.Help please! I'm so close to deploying this and now I'm bogged downin security. Any ideas anyone has would be appreciated. Accountadministration and Windows security is not my strong suit.Barb
View 2 Replies View RelatedThose 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 RelatedI 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.
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...
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
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
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
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?
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
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 RelatedI 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?
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
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
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 Relatedhow 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 .
We are having a conversation at work and the subject of load balancing with SQL came up. Right now we are running SQL Server 2014 on four (4) machines. I am using a AlwaysOn with Availability Groups (AG). Now I know that we can scale out the reading in AG by allowing the secondary serves to receive reads.
Is there a way to be able to do this with writes? Can I have in essences 2 masters that some how reconcile with each other? We are expecting a huge amount of writes in the near future and we need a way for SQL to handle the amount of traffic we are expecting with out any issues.
I explored the possibility of Peer - to - Peer replication; however, it seems that it would be more work if we are constantly making updates to the database scheme.
we currently use Backup Scripts from Ola Hallengren, It Says Full (non copy-only) and differential backups are performed on the primary replica. Full(Copy-only) backups and transaction log backups are performed on the preferred replica.
we currently do FULL(COPY_ONLY) Backup everyday and LOG Backups for every 15 min, is there any performance benefit on running the FULL (non copy-only) on the preferred replica .
I'm getting a file that essentially has a parameter array: a specific like would have three known values, and then x number of groups of four parts;
The question is how to handle logical groups of parameters; i though maybe by using a modulous on their itemnumber ...
So I can easily use DelimitedSplit8K to definitively find the first thee values, but how do I dynamically get X number of groups of four; eventually i need the first three fields combined with each quadgroup.
So a rough example of the desired output is this:
this:
'03,0000001,USD,010,81257946,,,015,121809761,,'is split into rows with the first three columns included in each row:
030000001USD01081257946NULLNULL
030000001USD015121809761NULLNULL
Here's a setup i've put together:
IF OBJECT_ID('tempdb.[dbo].[#AccountIdentifier]') IS NOT NULL
DROP TABLE [dbo].[#AccountIdentifier]
GO
CREATE TABLE [dbo].[#AccountIdentifier] (
[AccountIdentifierID] INT IDENTITY(1,1) NOT NULL,
[DateReceived] DATETIME NULL DEFAULT getdate(),
[RecordCode] VARCHAR(3) NULL,
[Code] ....
We have a server A and a server B setup with SQL2014 High Availability - working fine.
I need to add an additional listener to our new DR site.
What is the best way to do this without impacting production - can I just add the additional replica from nodea as a secondary replica?
I am planning to have AlwaysON Availability Groups setup between Server 1 and Server 2
Server 1 -->Publisher-->2014 SQL Enterprise edition-->Windows Std 2012 --> Always on Primary Replica
Server 2 -->Publisher(when DR happens)-->2014 SQL Enterprise edition-->Windows Std 2012 --> Secondary Primary
Server 4 as Subscriber
Server X as Remote Distributor ..
If i create Publications on Server1 (primary replica) to subscriber 4 servcer, will the publication be created automatically in Secondary Replica Server2 ? or do i have to create manullay using GUI/T Sql on Both Servers?
I have configured replication between Always ON Availability Groups (Listener) (PUBLISHER), remote distributor to XYZ SUBSCRIBER...with above link ...
Now, I want to know how to replicate Data from XYZ SERVER a PUBLISHER to Always ON Availability Groups (Listener) (SUBSCRIBER)? Distributor Database being on XYZEX:
XYZ SQL SERVER as PUBLISHER, and DISTRIBUTOR
to
Always ON Availability Groups (Listener) SUBSCRIBER...
I am planning to have AlwaysON Availability Groups setup between Server 1 and Server 2
Server 1 -->Publisher-->2014 SQL Enterprise edition-->Windows Std 2012 --> Always on Primary Replica
Server 2 -->Publisher(when DR happens)-->2014 SQL Enterprise edition-->Windows Std 2012 --> Secondary Primary
Server 4 as Subscriber
Server X as Remote Distributor ..
as of now , In the event of Disaster happens on Node 1 i will have to manage JOBS on Node 2 (backups, Maintenance, replication, day-day other Jobs)..Is there a way i can call all jobs to run from a remote server which executes on Active NODE(which ever is active)? so that in the event of disaster happens on node1, when node 2 becomes online and i don't have to do anything with SQL Agent or jobs on Node 2?
This is my first deployment of an always on availability group for SQL 2014 and I'm trying to get my custom backup procedure to handle all databases appropriately depending on the primary group. Basiscally I want the system databases and all databases that don't participate in the availability group to be backed up on both nodes and those that do participate backed up ONLY on the primary server. I've looked at the sys.fn_hadr_backup_is_preferred_replica funcation, but would like to only have to test for a single databases existance in the availability group. If the one database is in the group, only backup the system databases and those that don't participate, otherwise backup everydatabase. This would be the case for both full backups and transaction logs.
View 1 Replies View RelatedI need to calculate the last two columns (noofgrp and grpsize) No of Groups (count of Clientid) and Group Size (number of clients in each group) according to begtim and endtime. So I tried the following in the first Temp table
GrpSize= count(clientid) over (partition by begtime,endtime) else 0 end
and in the second Temp Table, I have
select
,GrpSize=sum(grpsize)
,NoofGrp=count(distinct grpsize)
From Temp1
The issue is for the date of 5/26, the begtime and endtime are not consistent. in Grp1 (group 1) all clients starts the session at 1030 and ends at 1200 (90 minutes session) except one who starts at 11 and end at 1200 (row 8). For this client since his/her endtime is the same as others, I want that client to be in the first group(Grp1). Reverse is true for the second group (Grp2). All clients begtime is 12:30 and endtime is 1400 but clientid=2 (row 9) who begtime =1230 but endtime = 1300. However, since this client begtime is the same as the rest, I wan that client to be in the second group (grp2) My partition over creates 4 groups rather than two.
I am setting up a 3 node cluster as part of an availability group.Initially I tested failover between nodes using SQL Management Studio and everything failed over successfully when I stopped a node, and I was still able to write queries. I started to test with an application which connects using an SQL user and whenever I would switch nodes, I would get login failed. I believe the cause of this issue is because the server logins SID's which are tied to the database are different than the server logins on the other nodes which resulted in login failed.how can I ensure that the server logins SID's are the same between nodes? Is there a way of copying this over or how is this supposed to be done? I read a little about contained databases where I could just set the login on the database itself vs. creating a server login but I would rather not go down that route.
View 4 Replies View RelatedI'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.