How to Find Out the Members of a Group

There are times when you want to know who the users are in a particular group. Depending on your level of privileges, there are various ways to get this information: 1. You can find out about it using the SQL Server Management Studio or Query Analyzer: EXEC xp_logininfo ‘DOMAIN\group_name’,’members’ To run this, you will need [...]

Read Full Post »

Altering Table Schema and Schema Ownership – SQL Server 2005

Do you see something like this in your database? DOMAIN\login.myTable Username.myView Username.someStoredProc Sometimes these objects are created ”accidently” under user’s schema. So how do we change it back to dbo or to other schema? ALTER SCHEMA dbo TRANSFER DOMAIN\login.myTable This will alter the schema of myTable to dbo. Want to dig more information for schemas [...]

Read Full Post »

Change Default Schema In SQL Server 2005/2008

I recently found out that some schemas in my SQL2005 box are not of the default dbo (this is apart from some pre-determined schemas that we have). This seems to happen when I run sp_adduser to add a login as a user of a particular database. CREATE LOGIN [DOMAIN\mylogin] FROM WINDOWS GO USE MyTestDB GO [...]

Read Full Post »

Unlock Login in SQL 2005/2008

Without changing password: ALTER LOGIN [login] WITH CHECK POLICY = OFF; ALTER LOGIN [login] WITH CHECK POLICY = ON; GO With password change: ALTER LOGIN [login] WITH password =’*****’ UNLOCK; GO http://msdn.microsoft.com/en-us/library/ms189828.aspx

Read Full Post »

Replication Monitor For Non-Admin

It’s a good news that SQL Server 2005 comes with a fixed database role replmonitor to allow non-admin staff (developers etc) to view the Replication Monitor. To set it up: 1. CREATE USER username in the distribution database 2. Assign replmonitor database role to the username Your developers etc should be able to check out [...]

Read Full Post »