.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

How to turn off 'sa' only for a single database?

Posted By:      Posted Date: May 22, 2011    Points: 0   Category :
How do I turn off or disable system admin 'sa' only for a particular database? I want to sa to connect to other databases. I have created a new login for the database and want to connect to the database only with that particular login.
Thanks & Regards, Kaushik Ganguli, MCTS (ASP.net, SQL Server)

View Complete Post

More Related Resource Links

Turn Alerts OFF on a single list


OK, I must be missing something here. 

If I turned on Alerts for one library and then decide I don't want to get the alerts anymore, how do I turn them Off?

Single-page restore/recover in an online database

I am working with SQL Server 2008 64-bit (10.0.2531) on Windows Server Datacenter 2008 SP2 (64-bit). The problems I am having almost certainly stem from my lack of understanding of how SQL Server's transaction log is handled during recoveries. I am using this document as a rough guide. Here' what I am doing: 1. I intentionally corrupt a single data page in a normal table, so that I can practice single-page recovery. 2. I attempt to read the corrupt block in SQL. As expected I receive an error. 1> select top 10 firstname from ds2.dbo.customers 2> go Msg 824, Level 24, State 2, Server IP-0AF647E0, Line 1 SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 4:896; actual 3:0). It occurred during a read of page (4:896) in database ID 5 at offset 0x00000000700000 in file 'C:\sql\dbfiles\cust1.ndf'. Additional messages in the SQL Server error log or system event log may provide more detail. 3. I take a log tail backup before I do anything to try to fix the corruption: 1> backup log ds2 to disk = 'c:\sqlbackup\ds2.bak' with norecovery 2> go Processed 5 pages for database 'ds2', file 'ds_log' on file 1. BACKUP LOG successfully processed 5 pages in 0.041 seconds (0.857 MB/sec). 4. I  restore the corrupt page from backup: 1> restore database ds2 page='4:896' 2> from disk='c:\sqlbackup\ds2.bak' 3> with norecovery 4&


the database result is as follows DATE     code   class1 class2  class3 1/8/10     M1     10       20     30     3/8/10     V2      20       30     10      1/8/10      H1      1           2      3         7/8/10     ! ! 8/8/10     !M2    5      5      5       3/8/10     B2      4      4     4       1/8/10     V1      3      7     3       THE OUTPUT SHOULD BE AS FOLLOWS:- DATE           M1                          M2           

Scaling Single Server Farm by adding a seperate Database server

Hi, Right now, I am having a sinle server farm SharePoint 2010 installation. Now I need to seperate out the SQL server from the same box and want to move it to the new SQL server. In short , I need to have a seperate SQL server in my current farm (which is single server farm installtion). Can some one point me to the steps for the same ? Thanks.

How to Listen to MSMQ and Database table from a single WCF service

Hi, I have one WCF service which is listenibg to MSMQ. Once message is added to MSMQ, it will start processing the business logic. But my requirement is to make this WCF service to listen to one database table also. If any message is logged to this table or any message added to MSMQ, the WCF service should start processing the business logic. Could you please let me know if it's feasible using WCF service. It would be great if you can tell me the alternate approaches to implement this functionality. Ram.  .NET related discussions

Move Single MOSS Content Database to Different SQL Server

We want to mmove the single database of MOSS on Network Test and Development to the Production SQL Server. But MOSS aplication server remains same!

So in this condition it points out for the two database servers at the same time.

Is it possible? Or I need to migrate the all concern databases to the another SQL Server?

Pls HelP!

Websites using shared libraries and single common database issues


Hi everyone,

    I will be having several websites using the same shared libraries(Domain,Repository,etc..) and one single database.

 Later on i want to add another website with some added functionalities and added tables,columns ,etc in the database. The issue is that when i go deploy the last website, i will have to update all my other projects so that they work.

Is there any work around this process? Or is this the right way of doing things? 

Thx everyone

Database Corrupted - Can't get db in single user mode

I have two SQL 2008 R2 databases running on a 64-bit development Machine :
I have created Synonyms in MyDatabase (Table1, Table2, Table3) for three tables in the IMS database.
I am experiencing very poor performance using these Synonyms from my application so I am trying to troubleshoot the problem.
I tried to run "DBCC CHECKDB (IMS,  REPAIR_ALLOW_DATA_LOSS)" by performing the following steps
  1. Used Services to stop the processes (Instance, Agent, Browser, Integration Services 10.0, VSS Writer)
  2. Ran cmd.exe "As Administrator"
  3. cd C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\Binn
  4. ran sqlservr.exe -m -s SQLEXPRESS (output says it started in single user mode)


  1. Ran cmd.exe "As Administrator"
  2. cd C:\Program Files\Microsoft SQL Server\100\Tools\Binn
  3. ran sqlcmd.exe -S D

Restore database from single disk to multiple disks?


We have expanded our drive layout on a particular server from 1 huge disk (10TB) to 8 (1.2TB) disks. 


My question is how do you restore a database and have the data spread across the 8 disks? We have about 10 DB's to restore so it could be quite a challenge.




Max files in a single WSS 3.0 Database (SBS 2008 Server - Window Internal DB)


This site appears to indicate that the max documents per database is 32,767 :


This seems extremely small to me. Is that an accurate number? We have a customer with 300,000 documents and about 30 users. Is this going to be too much for a single DB?

SQL Server 2000 Database in "single user mode"


Hi all,

I've an old production database server with SQL Server 2000 SP4, that runs from 6 years. Now sometimes I find that the production database goes in "single user mode". Why? My error logs don't help me.

Reconnecting existing configuration database to existing Single-server WSS 3.0 deployment


Hello, see below for my issue:

The problem:
  • Our company's WSS 3.0 sharepoint database recently became corrupted (still unsure what caused the corruption...). While the database was rebuilding we disconnected the farm. Now when we attempt to use the Sharepoint Products and Technologies Wizard to reconnect it asks for the Database Access Account for the configuration database. It is insisting that we use NT Authority\Network Service but if you do not supply a password (NT Authority\Network Service does not have a password) you cannot click next.

Steps we have tried:

  • We have also tried to use a local user that is under the db_owner server role and are still told to use NT Authority\Network Service.
  • We have also tried use psconfig.exe in the command line but get errors there as well when supplying no password.


Is there any way to reconnect to the farm?

Thank you for your help.

Is realtime SQL Server Replicated database usable realtime (not offline / single user mode)


Is SQL Server replicated (destination) database replicated by SQL Server Replication fully usable? Meaning it's not just for Disaster Recovery but is fully usable from various say BI / Reporting purposes.

In the essence it is exactly same as other replication engines and the destination database is very much usable and not just offline / single user mode.



SSMS 2008 - Is there a better way to display/group tables in multiple scheams under single database


In SQL server 2008 we have 30 users schemas setup under a single database (say "UserDB")

Each user is set up with access to his/her own schema

So when you login to server and drill down to Databases >>UserDb >> Tables,  you will see the tables that belong to your schema


Now I have to give cross Schema access. User 1 wants to see User 2's schema tables.

User 1 tables shows up as user1.table1 and user 2 tables show up as user2.table2


Question is: Instead of listing ALL the tables, differentiated by prefix, is there a way to show them like windows folders.

new query - braces, case sensitivity, database names now required? TURN IT OFF PLEASE?


call me stupid or whatever, i don't care.

i have found the new query editor in 2008 have the most restrictive and pain in the a$$ feature i have ever seen.
every query now requires brackets, fully qualified dabase name + table + column for reference.

WHY am i required to type the name of the database i'm querying in a stored procedure?  COMPLETELY POINTLESS!
PLUS on top of all of that, the junk is CASE SENSITIVE.  if i wanted to waste time worrying about caps, braces and meaningless syntactical characters,  i would write code in C#.

the reason why this is SUCH AN INCONVENIENCE is that the database i am writing against has HUNDREDS of tables.
additionally, i've used 2005 for years and i really find it highly inconvenient to use the same tool as before but now i need to write queries in proper english and case.

here's a quick example: 
previously i could type 'select top 5 zipcode from shipment
NOW with the new and improved pain in the a$$ feature i have to type:
'select top 5 '[databasename].[dbo].[shipment].[zipcode] from [databasename].[dbo].[shipment]'
that might not be an exact example but you get the idea.

maybe i just don't know what the new intellisense feature is that controls this?
somebody please help me figure out how to turn off this restriction, it is

SMO Database in Single User mode issues


I am trying to use the SMO library and setting the single user mode. Here is a simple example illustrating my issue.

The below code will fail the first time. Saying "Invalid object name TestTable". Eventhough that table does exist and so does the DB. However, if you run it again (now the DB is already in single user mode) everything works fine. Also, if you remove the code that alters the db user mode everything works fine. I am assuming the problem must exist with the single user mode and utilizing the correct db connection. I have tried flipping the AutoDisconnectMode and NonPooledConnection but that hasn't help. Any help would be appreciated.

Sample code

SqlConnectionStringBuilder sb = new SqlConnectionStringBuilder(); 

sb.Password = "P@ssword!"; 

sb.UserID = "testuser"; 

sb.DataSource = @".\mssql2008"; 

sb.InitialCatalog = "TestDB"; 

ServerConnection serverConn = new ServerConnection(); 

//serverConn.AutoDisconnectMode = AutoDisconnectMode.NoAutoDisconnect; 


How to Backup Single Server (Database on same server )Extra-net SharePoint Site MOSS 2007 SP1? and H



I have to create Test Server (Single server with Database on Same Server).

What Kind of Backup should i be asking  to Hosting Company,SharePoint MOSS 2007 SP1 Extra net site Hosted by 3rd Party.

How to restore it to Test Server and Has any one documented this process and has anyone had sucess in doing similar work ?

I do not have any access to Production server.How to make sure i have same server setup & configuration as Production Server.


I have to customize this site and do some development work so i thought this would be best approach ?


Please let me know..thanks..



ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend