Friday, May 22, 2015

Can not find the types 'x', because it does not exist or you do not have permission - SQL Server

Recently, when I was working with security of SQL Server, I faced one permission issue of custom data type and received an error below,
msg 15151, Level 16, State 1, Procedure xxx, Line 9
Cannot find the types 'x', because it does not exist or you do not have permission
This error received while executing stored procedure and this custom data type used in a stored procedure. The user has executed and appropriate permission of base tables, even it raised an error. Finally, I came to a solution and given permission below,
Use <YourDBName>
GO
GRANT VIEE DEFINITION ON TYPE :: DBO.X TO <User Name>
GRANT EXECUTE ON TYPE :: DBO.X TO <User Name>
GRANT CONTROL ON TYPE :: DBO.X TO <User Name>
GO
This is just what I faced and the solution applied to resolve it. Please share your comments if you ran into a custom data type issue.

Thursday, May 21, 2015

Fix Orphaned Users in SQL Server

You may aware of the orphaned users and it is experienced when restore a database backup to another server with logins which means the database user restored in a system which does not have associated valid logins. After restoring a database backup we can fix those orphaned users with sp_change_users_login. Let us see a small example to fix orphaned database users.
-- At source server
USE [master]
GO
-- Creating a test login
CREATE LOGIN [testlogin] WITH PASSWORD=N'testlogin', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE [DemoDB]
GO
-- Creating a database user for login testlogin created 
CREATE USER [testlogin] FOR LOGIN [testlogin]
GO
-- Assigning a database role to testlogin user created
ALTER ROLE [db_datareader] ADD MEMBER [testlogin]
GO

USE master
GO
-- Taking a database backup at source
BACKUP DATABASE [DemoDB] 
TO DISK = 'D:\DemoDB_full_20130913.bak'


-- At destination server
USE Master
GO
-- Restoring a database backup at source
RESTORE DATABASE [DemoDB] 
FROM DISK = 'D:\DemoDB_full_20130913.bak'
WITH replace,
MOVE 'DemoDB' TO 'D:\Data\DemoDB_data.mdf',
MOVE 'DemoDB_log' TO 'D:\Log\DemoDB_log.ldf'


USE DemoDB
GO
EXEC sp_change_users_login 'report'
GO

/*
UserName UserSID
----------------------------------------------
testlogin 0xFD13E649B5EF75469A22D598C8E0790D
*/

-- Mapping a user to new sql login
USE DemoDB
GO
CREATE LOGIN testlogin WITH PASSWORD = 'testlogin'
GO
EXEC sp_change_users_login 'Update_One','testlogin','testlogin'
GO

-- Automatically mapping a user to login, creating a new login if not exists
USE DemoDB
GO
EXEC sp_change_users_login 'Auto_Fix','testlogin',NULL,'testlogin'
GO

/* Output
-- If login does not exists
Barring a conflict, the row for user 'testlogin' will be fixed by updating its link to a new login.
The number of orphaned users fixed by updating users was 0.
The number of orphaned users fixed by adding new logins and then updating users was 1.

-- If login already exists
The row for user 'testlogin' will be fixed by updating its login link to a login already in existence.
The number of orphaned users fixed by updating users was 1.
The number of orphaned users fixed by adding new logins and then updating users was 0.
*/

You can fix all users at one shot using the following script,
USE [DemoDB]
GO

CREATE TABLE #OrphanedUsers
    (
         Id    INT IDENTITY(1, 1),
         UserName VARCHAR(250)
    )

DECLARE @i INT, 
  @Total     INT,
        @User VARCHAR(250)

INSERT INTO #OrphanedUsers (UserName)
SELECT DISTINCT [Name]
FROM   [Sysusers]
WHERE  Islogin = 1
       AND [Name] NOT IN 
  ( 'guest', 'sa', 'dbo', 'public',
          'sys', 'INFORMATION_SCHEMA' )

SET @Total = @@ROWCOUNT
SET @i = 1

WHILE ( @i <= @Total)
  BEGIN
      SELECT @User = UserName
      FROM   #OrphanedUsers
      WHERE  Id = @i

      EXEC sp_change_users_login 'Auto_Fix', @User, NULL, @User

      SET @i = @i + 1
  END

DROP TABLE #OrphanedUsers
Hope you enjoyed this small example and would like to you share ideas for same.
Stay tuned for more!
X