Msg 15151, Level 16, State 1, Line 2
Cannot alter the login ‘sa’, because it does not exist or you do not have permission.
This error had occurred because of insufficient rights. Please read my previous post here before reading further article.
SA is system admin user and it is the highest level of user in system. If any user have to modify the permissions of SA that user needs to have higher or equivalent rights as SA user. Users member of systemadmin group are can only change the rights of SA user.
First adding any user to systemadmin role and then using the same user to modify system admin’s tool will fix the issue.
GO /* Create Test Table */ CREATE TABLE TestTable (FirstCol VARCHAR(10)) GO /* Check Database Column Collation */ SELECT name, collation_name FROM sys.columns WHERE OBJECT_ID IN ( SELECT OBJECT_ID FROM sys.objects WHERE type = 'U' AND name = 'TestTable') GO /* Change the database collation */ ALTER TABLE TestTable ALTER COLUMN FirstCol VARCHAR(10) COLLATE SQL_Latin1_General_CP1_CS_AS NULL GO /* Check Database Column Collation */ SELECT name, collation_name FROM sys.columns WHERE OBJECT_ID IN ( SELECT OBJECT_ID FROM sys.objects WHERE type = 'U' AND name = 'TestTable') GO /* Database Cleanup */ DROP TABLE TestTable
When ran above script will give two resultset. First resultset is before column’s collation is changed and it represents default collation of database. Second result set is after column’s collation is changed and it represents newly defined collation.
Let me know what are your ideas about collation and any problem if you have faced for the same. I am interested to share those with the SQL community.
The RML utilities allow you to process SQL Server trace files and view reports showing how SQL Server is performing. For example, you can quickly see:
Which application, database or login is using the most resources, and which queries are responsible for that
Whether there were any plan changes for a batch during the time when the trace was captured and how each of those plans performed
What queries are running slower in today’s data compared to a previous set of data
You can also test how the system will behave with some change (different service pack or hotfix build, changing a stored procedure or function, modifying or adding indexes, and so forth) by using the provided tools to replay the trace files against another instance of SQL Server. If you capture trace during this replay you can use the tools to directly compare to the original baseline capture.
Microsoft has published following three security related white papers. I suggest to all my readers to read them. Read the summary know what is covered in those white papers.
Engine Separation of Duties for the Application Developer- Separation of duties is an important consideration for databases and database applications. By properly defining schemas and roles, you can create a distinction between users who can manipulate data from those that administer the database. This paper discusses the topics of which application developers should be aware and provides a heuristic example to guide you in achieving separation of duties.
Database Encryption in SQL Server 2008 Enterprise Edition – With the introduction of transparent data encryption (TDE) in SQL Server 2008, users now have the choice between cell-level encryption as in SQL Server 2005, full database-level encryption by using TDE, or the file-level encryption options provided by Windows. TDE is the optimal choice for bulk encryption to meet regulatory compliance or corporate data security standards. TDE works at the file level, which is similar to two Windows features: the Encrypting File System (EFS) and BitLocker Drive Encryption, the new volume-level encryption introduced in Windows Vista, both of which also encrypt data on the hard drive. TDE does not replace cell-level encryption, EFS, or BitLocker. This white paper compares TDE with these other encryption methods for application developers and database administrators. While this is not a technical, in-depth review of TDE, technical implementations are explored and a familiarity with concepts such as virtual log files and the buffer pool are assumed. The user is assumed to be familiar with cell-level encryption and cryptography in general. Implementing database encryption is covered, but not the rationale for encrypting a database.
Cryptography in SQL Server – Although cryptography provides SQL Server with powerful tools for encryption and verification, these are often not well understood. This can lead to poor or incomplete implementations. This white paper presents an overview of cryptographic functionality and discusses how this applies to authentication, signed procedures, permissions, and encryption. Because the target audience is the database professional and not necessarily security experts, the focus is on practical ways to use cryptography in SQL Server.
Recently I have got two desktop computers at home and both of them are very powerful machine.
Machine 1 : Windows Vista SP1 with SQL Server 2008
Machine 2 : Windows 2003 with SQL Server 2005 with SP2
When I was trying to connect from SQL Server 2008 to SQL Server 2005 using Windows Authentication I was getting following error.
Login failed for user ‘UserName’. The user is not associated with a trusted SQL Server connection.
To resolve this error follow the steps below on computer with SQL Server 2005.
Create new user with Administrator privilege with same username and password as of SQL Server 2008 in operating system.
On SQL Server database create new user by expanding DatabaseNode >> Security >> Login >> Create New User and add this new user with Windows Authentication radio button selected. This user can be only added by selected Windows Authentication it is Operating system’s User Login.
Once above two steps are completed when connected from SQL Server 2008 to SQL Server 2005 using Windows Authentication it will connect successfully.
Stored Procedures are very important and most of the business logic of my applications are always coded in Stored Procedures. Sometime it is necessary to hide the business logic from end user due to security reasons or any other reason. Keyword WITH ENCRYPTION is used to encrypt the text of the Stored Procedure. One SP are encrypted it is not possible to get original text of the SP from SP itself. User who created SP will need to save the text to be used to create SP somewhere safe to reuse it again.
What prompted me to write this article is when SP are encrypted Actual Execution Plan of the SP is also not visible. In fact, it does not respond to that command and does not display the tab of execution plan if only Encrypted SP is called.
To understand this scenario let us create two SPs. First without encryption and second with encryption. Once SPs are created run them one by one with having Actual Execution Plan turned on. To learn how to turn on Actual Execution Plan read my previous article SQL SERVER – Actual Execution Plan vs. Estimated Execution Plan.
/* Create SP without Encryption */ CREATE PROCEDURE #RegularSP AS
SELECT TOP 10 City FROM Person.Address
GO /* Create SP with Encryption */ CREATE PROCEDURE #EncryptSP WITH ENCRYPTION AS
SELECT TOP 10 City FROM Person.Address
GO /* Execute SP - Execution Plan Tab shows up */ EXEC #RegularSP GO
/* Execute SP - Execution Plan Tab does not shows up */ EXEC #EncryptSP GO
/* Clean Up */ DROP PROCEDURE #RegularSP DROP PROCEDURE #EncryptSP GO
It is very clear from above example that Encrypted SP does not show the actual execution plan as from Actual Execution Plan user can figure out the logic behind the SP. To avoid this issue for encrypted SP Management Studio does not display the execution plan.