Voice of the DBA

Tag: SQLNewBlogger

Am I a sysadmin?–#SQLNewBlogger

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

I was doing some security testing and wondered if I was a sysadmin. There are a few ways to check this, but I thought there should be a function to tell me.

There’s this code, of course:

SELECT
ServerRole = rp.name,
PrincipalName = SP.name
FROM sys.server_role_members rm
Inner JOIN sys.server_principals rp
ON rm.role_principal_id = rp.principal_id
Inner JOIN sys.server_principals SP
ON rm.member_principal_id = SP.principal_id
where sp.name = SUSER_SNAME()
and rp.name = ‘sysadmin’

That lets me know if my login is a sysadmin. However, there is a function that you can use. IS_SRVROLEMEMBER() is a function that you can use, passing in a server role as a parameter. The code I’d use to check on sysadmin membership is this:

SELECT IS_SRVROLEMEMBER(‘sysadmin’);

If I run this, I get a 1 if I’m a member, or a 0 if I’m not.

Using this function in your code allows you to make decisions based on role membership for the users involved, and perhaps alert them of needs for certain rights.

SQLNewBlogger

This was a quick one, really about 10 minutes to organize and write. Most of the time was writing the code to join system tables. If you tackle this subject, talk about how you might use this, or where this type of check could come in handy in your code (maybe before taking some action).

April 28, 2016
Changing the sa Password with SQLCMD

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

I wanted to make a quick note on changing the sa password, as this is a sensitive account, and the password should be changed if you ever suspect it is compromised. I’d also recommend you change this if anyone that knows the password leaves your group.

I wrote about using SSMS, but that’s not always convenient. If you need to change this remotely, perhaps in a hurry, SQLCMD is a quick way to do this.

SQLCMD is a command line tool, so open a command prompt.

Run SQLCMD and connect to your instance as a sysadmin. If you have any doubt, you can enter the query from my previous post to check your connection.

Once you’ve connected, you can issue this code:

ALTER LOGIN [sa] with PASSWORD = N‘Sup#rAmaz!ngP@$$w0rd’

This is the code that will change the password for the login specified, even if I’ve logged in with a different account.

Once I’ve done this, test the sa login from a new session and verify it works.

SQLNewBlogger

Make sure you know how to do this. It’s a basic skill, so learn it, blog about it, and use it where appropriate. Maybe write about why you’d do this in your own post.

References

SQLCMD – https://msdn.microsoft.com/en-us/library/ms162773.aspx

ALTER LOGIN – https://msdn.microsoft.com/en-us/library/ms189828.aspx

April 22, 2016
Basic SQLCMD–#SQLNewBlogger

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

I had the need to connect from the command line recently, and decided to make a quick post on using SQLCMD, as I had an issue.

SQLCMD is a command line utility that comes with SQL Server. I know many people don’t use command lines, but they are handy at times. I recently opened a command prompt.

I then typed SQLCMD. After a delay, I got this:

The issue here is that I don’t have a default instance on this machine. All of mine are named. I need to provide a –S parameter, with a server name (and possibly instance name).

I do that and I’m connected. By default, SQLCMD (and osql) try to use Windows Auth. The 1> indicates that the utility is ready for T-SQL queries. You need to know your language here as there’s no help.

I can enter code, and check my user name. I do this, and get a 2>. The end of a batch is indicated with “GO” and this will execute the batch. You can see how this works below:

I can use this to make my code easier to read. I can format code as I would in an editor, though be aware you can’t go back and edit previous lines.

If I enter go, I’ll get this:

Not so easy to read. I have to scroll up to even figure out what the display is:

As you can see, using SELECT *, or retrieving too many columns make results hard to read. You would to wise to pick only those columns you need to return.

To leave SQLCMD, you can type exit, which will return you to the command prompt.

This is a short look at SQLCMD. The older, osql, utility functions the same way, and both are good, lightweight ways to connect to your SQL Server instance.

April 14, 2016
Changing the sa Password with SSMS–#SQLNewBlogger

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

I wanted to make a quick note on changing the sa password, as this is a sensitive account, and the password should be changed if you ever suspect it is compromised. I’d also recommend you change this if anyone that knows the password leaves your group.

Changing sa with SSMS is really simple. Follow these steps:

Connect to SSMS as a sysadmin. You can check this for your login. Then expand the Security folder and the Logins folder. Right click the “sa” account and choose properties.

Once you do this, you’ll get the login properties dialog, and see the Password text field at the top in the General tab.

You can type a new password in the Password box, and confirm this in the Confirm Password box. Password policies checks are up to you, though I’d recommend you use them.

Click OK and the password is changed. You can then connect a new query window as “sa” and verify your password.

SQLNewBlogger

Make sure you know how to do this. It’s a basic skill, so learn it, blog about it, and use it where appropriate. Maybe write about why you’d do this in your own post.

April 11, 2016