Skip to main content

Security � How To Assign Rights To SQL Server Database Objects

Piotr Rodak has a great article on Security – How To Assign Rights To SQL Server Database Objects  and why you should not assign rights to the public role and why assigning rights to roles instead of users gives you much more flexibility. He also covers why you should only grant permissions to your stored […]

Searching SQL Stored Procedure Source Code in Information_Schema.Routines

— Here’s a little line of code I use a lot to find out what SQL stored  procedures / functions use a particular table SELECT * FROM   information_schema.routines WHERE  routine_definition LIKE ‘%GetThemeColours%’ –&nnbsp;INFORMATION_SCHEMA.ROUTINES is similar to sys.routines but  has all the text names instead of objectid’s  with the added bonus the full  stored procedure text is searchable (its in ROUTINE_DEFINITION column) – its like  a super powered sp_helptext — Also check out these helpful views: — View all the columns in tables + views with full table/view name SELECT * FROM   information_schema.columns — stored procedure / function parameters SELECT * FROM   information_schema.parameters — output columns for table valued functions (NOT stored procedures) SELECT * FROM   information_schema.routine_columns — list of tables SELECT * FROM   information_schema.tables — list of views SELECT * FROM   information_schema.tables    — 195cac785d844883be7a61f2b54189c6

Queensland SQL Server User Group Meeting Tonight

QSSUG Meeting This Thursday – 26th of November   The emerging field of "Location Intelligence" brings the worlds of Geographic Information Systems (GIS) and Business Intelligence (BI) together. These capabilities are now available with SQL Server in a no-coding, user-driven environment provided by the Integeo ( Map Intelligence product. SQL Server 2008 also introduces a […]

Answer: How do you create a byte with the value of 00000001 in SQL Server?

My team mate Pi asked me today: how do you create a byte with the value 00000001 in SQL Server? The answer is to create an integer representation of the byte’s value, and cast it to binary. SELECT CAST(CAST(1 AS TINYINT) AS BINARY(1)) The output in Management Studio is: 0x01 (Management Studio outputs binary fields […]

Hey Yo! SQLCMD may truncate your XML!

While trying to use SQLCMD to produce an app.config file, I encountered problems with the output being truncated. The script was similar to: DECLARE @appconfig XML SET @appconfig = ‘<configuration></configuration>’ … Insert configuration nodes — Output the config SELECT @appconfig as ApplicationConfiguration The default options for SQLCMD produced output like: ApplicationConfiguration ————————- <configuration><connecti (the value […]

How to Connect to a SQL Server Compact Edition Database with Management Studio

How to Connect to a SQL Server Compact Edition Database with Management Studio

Open a new connection by choosing File menu -> Connect Object Explorer in SQL Server Management Studio. Change the Server Type to SQL Server Compact Edition. For Database file: put the full path to an existing SQL Server Compact Edition .sdf file; click the drop-down button, and select New Database or Browse to locate a […]

SQL Server 2005 – Service Pack 2a (SP2a) Update

Downloaded and installed Service Pack 2 for SQL Server 2005? Well here’s a reason to download and apply it again. A few days after releasing Service Pack 2 for SQL Server 2005, Microsoft has re-released it with an additional fix for maintenance plans. If you have already applied SP2, you will need to apply this […]

Suppressing Warnings in Data Dude

Suppressing Warnings in Data Dude

I’ve been working on migrating existing SQL Server database projects into Visual Studio for Database Professional projects. Most of the existing databases have imported without major problems, but there are a few that have warnings about cross-database joins in old unit tests. TSD3025: The following cross-database dependencies could not be verified: … Your database application […]