John Stagich's Blog

Microsoft .Net Developer

October 2010 Quick Hits

clock October 1, 2010 14:48 by author JohnStagich

I changed the name of a database in Microsoft Sql Server 2005.  The database happened to be the default database for a user.  The next time the user tried to connect to the SQL server instance, they received the following error message: Cannot Open User Default Database.  Here is the fix.

SQL Server 2005

You can use the sqlcmd utility to change the default database in SQL Server 2005. To do this, follow these steps:

  1. Click Start, click Run, type cmd, and then press ENTER.
  2. Use one of the following methods, depending on the kind of authentication that the SQL Server login uses:

    If the SQL Server login uses Microsoft Windows authentication to connect to the instance, type the following at the command prompt, and then press ENTER:

    sqlcmd –E -S InstanceName –d master

    If the SQL Server login uses SQL Server authentication to connect to the instance, type the following at the command prompt, and then press ENTER:

    sqlcmd -S InstanceName -d master -U SQLLogin -P Password

    Note InstanceName is a placeholder for the name of the SQL Server 2005 instance to which you are connecting. SQLLogin is a placeholder for the SQL Server login whose default database has been dropped. Password is a placeholder for the SQL Server login password.
  3. At the sqlcmd prompt, type the following, and then press ENTER:
    ALTER LOGIN SQLLogin WITH DEFAULT_DATABASE = AvailDBName
    Note AvailDBName is a placeholder for the name of the existing database that can be accessed by the SQL Server login in the instance.
  4. At the sqlcmd prompt, type GO, and then press ENTER.

 

  • Custom Sort from Andy Warren.  How to get United States to appear first in a drop down list of countries. 

    Select CountryName
    From Countries
    order by
      Case when CountryName = 'United States' then 0 else 1 end,
      CountryName

     
  • I was having trouble parsing an Update query that was using an inner join.  It turned out that I was missing the From clause in my update statement.
Update Tablename
Set Field1 = 100
Inner Join --Wrong
Update Tablename
Set Field1 = 100
Fom Tablename
Inner Join --Correct 
  • In Sequel Server Reporting Services (SSRS), how to hide a textbox (follow link, go to using Complex Expressions, about halfway down within Complex Expressions, Hide a text box on the design surface...) based on a boolean field value in the recordset:
Open Textbox properties Dialaog box, click on Visibility tab, and enter =Not fields!VoidFlag.Value in the Expression box.   



SQL Database Security Using Windows Authentication

clock October 1, 2010 08:20 by author johnstagich

I was setting up security for general users on a production Microsoft SQL database. The security configuration needed to allow the users to execute stored procedures called from a WPF application. Here are the steps I took using Windows Authentication.

1) At the database security level, I did not assign the users to any specific database roles, so the users, by default, were assigned to the public database role, which provides minimum permissions and privileges.

2) Granted Execute Permissions on the stored procedures:  Grant Exec On StoredProcedureName To User/AcitveDirectoryGroup  (We assigned the users to an Active Directory group).   Below is some sql code to simplify the process:

SELECT 'GRANT EXEC ON ' + name + ' TO ' + '[ ActiveDirectoryGroupName ]'
FROM sysobjects
WHERE type = 'P' AND category = 0 And name not Like 'sp_%'
Order By name

3) Deny view capabilities to the public role:

Use DatabaseName
GO
DENY VIEW DEFINITION TO public;  

Further Refinded:

DENY VIEW DEFINITION TO [ActiveDirectoryGroup]
GRANT VIEW DEFINITION TO [ActiveDirectoryGroup] 


I subsequently found other ways to grant Exec privileges through the creation of a db_executor role.

 a) Grant Exec at the database level through a db_executor role      
              
     CREATE ROLE db_executor
     GRANT EXECUTE TO db_executor   exec sp_addrolemember 'db_executor','YourUser'  
 
b)  Grant Exec at the schema level

       GRANT EXECUTE on schema::dbo TO db_executor



About the author

I am the owner of Stagich Software Consulting.  Stagich Software Consulting specializes in developing Microsoft .Net/Microsoft SQL software applications.

 

Calendar

<<  March 2024  >>
MoTuWeThFrSaSu
26272829123
45678910
11121314151617
18192021222324
25262728293031
1234567

View posts in large calendar

Month List

Disclaimer

The opinions expressed herein are my own personal opinions and do not represent my employer's view in anyway.

Sign In