Category Archives: SQL

How to: Find users in SQL having DB Owner role

Find members of db_owners in all databasesThis script is aim to provide all members of the db_owner database role in all databases in an instance. It
The following code returns the role name and the member name filtered for the db_owner database

This script is aim to provide all members of the db_owner database role in all databases in an instance. It uses sp_MSForEachDb with a custom script using sys.database_principals DMV. The following code returns the role name and the member name filtered for the db_owner database role.

exec sp_msForEachDb ' use [?]
select db_name() as [database_name], r.[name] as [role], p.[name] as [member] from
sys.database_role_members m
join
sys.database_principals r on m.role_principal_id = r.principal_id
join
sys.database_principals p on m.member_principal_id = p.principal_id
where
r.name = ''db_owner'''

(673)

How to: Execute SQL stored procedures using command line

Often when having SQL Express Editions installation I would like to schedule and execute stored procedures. Using Express editions you cannot make use of the SQL Agent, but you can run them manually using a batch file and using the SQLCMD.EXE file as below

SQLCMD /Q "USE mydb; EXEC mystoredprocedure"

This will use the mydb that you specify and run the mystoredprocedure. You can also use the > filename.txt to make the job log.

(664)

How to: Execute SQL Stored procedure from command line

When coming accross SQL Express editions, you will not be able to set a job in the Agent to execute a stored procedure. I usually do custom stored procedures to run them on Express editions.

There is a way to execute them by using the SQLCMD command

sqlcmd -Q "exec myprocedure" -S mysqlserver -d fromthisdatabase -E -o C:\log.txt

This will execute your stored procedure and you can put it in a batch file and schedule it in the Task Scheduler

(5929)

Fix: Cannot run or delete a job in SQL after changing sa password

After changing the password of the SA or the user which is in charge of running jobs on the SQL server, the jobs will not work and you will not be able to delete the jobs with the error ‘Login failed for user SA‘. This can temporary solved by changing the SA or the user used password back to it’s original state and delete the job, but still you will not be able to run the job.

To solve this permanently you have to open the ‘Management Studio’, right-click on the job and select ‘Modify’. Click on the ‘Manage Connections’ and replace the username and password on each connection if you have more than the default one using the new password that you have recently changed.

This modification you have to do it for every-time you change the password of the ‘sa’ or user you use to run the jobs in the Manage Connections.

(648)

How To: Backup databases for SQL 2000 MSDE with SQLMaint.exe

To Backup a database from command-line and using SQL 2000 MSDE version, you must use the SQLMAINT.EXE file situated in the ‘C:\Program Files\Microsoft SQL Server\MSSQL\Binn’

The backup cannot be done for all databases at once and it must be done individually. This can be done by the line below and then just duplicate it for multiple databases.

Create a batch file and run the following:

CD "C:\Program Files\Microsoft SQL Server\MSSQL\Binn"
sqlmaint -D "MyDB" -BkUpDB "X:\Backups\Data" -Rpt "X:\Backups\Reports\MyDB.txt" -BkUpMedia DISK

(1502)

How To: Execute and run files from a stored procedure in SQL Server

Sometimes when you need to automate some stuff from the SQL server you need to create a batch file and run it in the Task Scheduler. This way you can run the jobs you need to do and then run the executable or batch file you need from a Stored Procedure.

Firstly you need to make sure that the XP_CMDSHELL is enabled. This will allow you to execute the files from SQL.

This can be easily done from the Surface Area Configuration tool or by executing the following code:

EXEC master.dbo.sp_configure ‘show advanced options’, 1
RECONFIGURE
EXEC master.dbo.sp_configure ‘xp_cmdshell’, 1
RECONFIGURE

This should enable the XP_CMDSHELL. Now to execute a file you need to add the following SQL code to your present Stored procedure:

DECLARE @myfile varchar(200)
SET @myfile = ‘C:\sysfiles\copytootherserver.bat’
EXEC master..xp_cmdshell @myfile

(8252)

How To: Automated shrink transaction logs in SQL

Making a scheduled automated shrink logs for the databases with specific space to shrink.

DECLARE @Step1 VARCHAR(200)
DECLARE @Name varchar(50)

DECLARE DBNames CURSOR
FOR
SELECT NAME FROM sysdatabases WHERE dbid > 4
OPEN DBNames

FETCH NEXT FROM DBNames INTO @Name WHILE (@@FETCH_STATUS <> -1)

BEGIN

SET @Step1 = 'USE ' + @Name + '' + CHAR(10)
SET @Step1 = @Step1 + 'ALTER DATABASE [' + @Name + '] SET RECOVERY SIMPLE;' + CHAR(10)
SET @Step1 = @Step1 + 'DBCC SHRINKFILE (' +@Name + '_LOG, 500);' + CHAR(10)
SET @Step1 = @Step1 + 'ALTER DATABASE [' + @Name + '] SET RECOVERY FULL;' + CHAR(10)

EXEC (@Step1)

FETCH NEXT FROM DBNames INTO @Name
END

CLOSE DBNames
DEALLOCATE DBNames

(54851)