Showing posts with label grant. Show all posts
Showing posts with label grant. Show all posts

Thursday, 28 May 2015

Sharing/Unsharing remote server's folder with remote stored procedure execution in SQL Server by serverku

Recently, when I was working with database backup plans and scheduling it on another server as this shared folder of the server should not be available to anyone after the backup process completion. I have created below script with stored procedure on remote server and run the stored procedure remotely from local server to share and unshare the remote server's folder. Please note xp_cmdshell and Ad Hoc Distributed Queries should be enabled on the server.

USE MASTER
GO

-- Creating shared
EXEC XP_CMDSHELL 'NET SHARE SharedFolder=E:\DatabaseBackups
/GRANT:Everyone,Full
/REMARK:"Database backups perform on another server"'
GO

-- Removing shared
EXEC XP_CMDSHELL 'NET SHARE SharedFolder /delete"'
GO

Thursday, 21 May 2015

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

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.