Microsoft sql server error 15247



You are not able to reference a user defined data type that is defined in model database in tempdb

Symptoms

Consider the following scenario:

You create a user-defined data type in the model database.

A user who is a not a system administrator tries to use this data type to create a temporary table in the model database.

In this scenario, the user will get the following error message

Msg 15247, Level 16, State 4, Server , Line 1

User does not have permission to perform this action.

Cause

The Books Online topic for CREATE TABLE has the following note under Permissions section:

If any columns in the CREATE TABLE statement are defined to be of a CLR user-defined type, either ownership of the type or REFERENCES permission on it is required.

This note is true not only for CLR data types but also for any user-defined data type (UDT). Refer to the topic titled “Using User-defined Types Across Databases” in the SQL Server Books Online regarding the behavior of CLR data types.

NOTE: You encounter this problem only when explicitly creating tables and not when implicitly creating tables using SELECT INTO statements.

Resolution

Grant permissions for user defined data type to the appropriate users as suggested in the Cause section. You can also workaround the problem by using one of the following methods.

Method 1: Grant REFERENCES permission to the public user in the model database.

CREATE TYPE dbo.udt_money FROM varchar(11) NOT NULL ;
go
GRANT REFERENCES ON TYPE::dbo.udt_money TO public

NOTE: Before using this method carefully evaluate the security implications since this permission gets carried over to every new database.

Method 2: If you do not want every new database to retain the definition and permissions for this user- defined data type, you can use a startup stored procedure to create and assign the appropriate permissions only in tempdb database.

CREATE PROCEDURE setup_udt_in_tempdb

EXEC ( ‘USE tempdb;

CREATE TYPE dbo.udt_money FROM varchar(11) NOT NULL;

GRANT REFERENCES ON TYPE::dbo.udt_money TO public;’)

EXEC sp_procoption ‘setup_udt_in_tempdb’ , ‘startup’ , ‘on’

Instead of using temporary tables, consider using table variables when you need to reference user-defined data types for temporary storage needs. For table variables to reference user-defined data types, you do not need to explicitly grant permissions for the user-defined data type.

Источник

Microsoft sql server error 15247

This forum has migrated to Microsoft Q&A. Visit Microsoft Q&A to post new questions.

Answered by:

Question

I was able to do my work with ease on my Database server, until sometime Friday evening. Don’t exactly know what transpired, only that I can now no longer run queries or even create new databases. Here is a sample of what happens when I try to create a database

Create Database PetitionSample

Msg 262, Level 14, State 1, Line 1

CREATE DATABASE permission denied in database ‘master’.

I used to be able to create databases, create new users and so on. My Server is installed on a Vista machine and I am running Developer version and using Management studio 2008 to access it. Anyone have any idea what could be going on?

Answers

  • Proposed as answer by Raul Garcia — MS Microsoft employee Friday, January 16, 2009 6:42 AM
  • Marked as answer by Mangal Pardeshi Monday, January 26, 2009 4:30 AM

All replies

  • Proposed as answer by Raul Garcia — MS Microsoft employee Friday, January 16, 2009 6:42 AM
  • Marked as answer by Mangal Pardeshi Monday, January 26, 2009 4:30 AM

I tried the examples of the page and got the same error each time

An Error Occured while executing a Transact-SQL Statement or batch
User does not have permission to perform this action( Microsoft SQL Server, Error:15247) None

No, You don’t need to install again, you just need to give rights to your login by logging in with sysadmin user. If server is running on mixed mode, log in with sa user and give your login required permissions.

Читайте также:  Polarssl ssl read error x509

First try to log in with sa, if sa is not enable, tell us, I will guide you accordingly. Mangal Pardeshi. You can turn off the SUN, but I’m still gonna shine. 😉

Did you guys solve this thing in the end?

I’m currently trying to do the same — having a similar issue getting the correct permission in SSME 2008 — right clicking so I can work as system admin — still no luck — final explaination confused me — being a newbie.

If your sa account is disabled , login with windows authentication and make sure you have your authentication mode set to MIXED mode and now you can enable your sa login using

ALTER LOGIN [sa] ENABLE

Thanks for responding. Appreciated.

Alas, in following your instructions I have a problem when I attempt to change authentication mode to mixed.

The message being:

TITLE: Microsoft SQL Server Management Studio
——————————

Alter failed for Server ‘PMC-PC\sqlexpress’. (Microsoft.SqlServer.Smo)

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

xp_regread() returned error 5, ‘Access is denied.’
xp_regread() returned error 5, ‘Access is denied.’
xp_regread() returned error 5, ‘Access is denied.’ (Microsoft SQL Server, Error: 22001)

Needless to say, at this stage I have no idea what the resolution to all this is, so any more thoughts would be extremely appreciated!

When you change some server level settings with respect to SQL server , that has to be updated in the windows registry.

This kind of server level changes like authentication modes are to be done by a login that has sysadmin privilege in the SQL server . By default sysadmin logins has got privileges to execute xp_regwrite

Stored procs . If you are using windows vista , try starting the SSMS with run as administrator option.

Bottom line : this is a permission issue so perform this action with sysadmin account and you will succeed , remember to restart SQL services once the authentication mode is changed.

I’m having the same problem on Windows 7. Sql doesn’t recognize Windows 7 Administrative privileges so it denies permission to grant any changes to Sql Server. Can’t add users, can’t change permissions for users. Can’t use sa ID because don’t know the password set up by the system. It’s not my Windows admin password. It’s a viscious circle — want to add user with admin level permission but have to have admin level permission to do so.

Thanks for any help.

I am stuck in this same scenario. I am a local administrator on the box, in the administrators group. I cannot login as sa because the account is disabled and because mixed-mode authentication is off. It will not let me enable the user, or enable mixed mode authentication (the xp_regread() error). I cannot add «sysadmin» rights to any logins because «User does not have permission to perform this action.» I even tried changing the service to run as me instead of Network Service. I tried running as the user named «Administrator»

It is a bit strange that I can modify any single bit on the entire hard drive, delete, overwrite, and modify files. Yet I can’t change the permissions on something. This has to be a bug of some kind. FYI: This is SQL Server 2008 Express x64 w/ SP2 on Windows 7 x64.

I found a solution. Basically, run SQL server in Single-user maintenance mode, and you will get access to the server again. You must be an administrative user for this to work.

NOTE: In all of the examples below, you may have to change parameters or command-lines based on your server name and instance name.

I. Force SQL server to support mixed-mode authentication.
1. Run REGEDIT
2. Go to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQLServer
NOTE: This key may vary slightly based on the installed version and instance name.
3. Set «LoginMode» to 2.
4. Restart SQL Server.
(Source: http://support.microsoft.com/kb/285097 )

Читайте также:  Find mysql error log

II. Force SQL server to let you in temporarily
1. Go to services.
2. Stop SQL Server.
3. Grab the SQL server command-line (right click the service — properties). Mine is:
«C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\Binn\sqlservr.exe» -sSQLEXPRESS
4. Open an administrative command prompt.
5. Run the command-line from step 3, but add -m -c for single-user maintenance mode command-line.
6. Open another administrative command prompt.
7. Run «sqlcmd -S localhost\SQLEXPRESS» from that same directory (replace with your server and instance name)
8. Now you can do all the stuff everyone told you to do that didn’t work. For example, to create a hero user with administrative accss:

9. QUIT and close the command-prompt
10. Go to the SQL Server command-line window and hit ctrl+C. It will prompt «Do you wish to shutdown SQL Server (Y/N)?» and enter Y.
11. Close the command-prompt
(Source: http://msdn.microsoft.com/en-us/library/dd207004.aspx )

III. Finally, login using your hero:
1. Restart the SQL Server service
2. Login using SQL Server authentication as the user «hero» with password «123»
3. *BAM* now you are in. Now give yourself sysadmin access and delete the temporary user.

Источник

Microsoft sql server error 15247

This forum has migrated to Microsoft Q&A. Visit Microsoft Q&A to post new questions.

Answered by:

Question

I was able to do my work with ease on my Database server, until sometime Friday evening. Don’t exactly know what transpired, only that I can now no longer run queries or even create new databases. Here is a sample of what happens when I try to create a database

Create Database PetitionSample

Msg 262, Level 14, State 1, Line 1

CREATE DATABASE permission denied in database ‘master’.

I used to be able to create databases, create new users and so on. My Server is installed on a Vista machine and I am running Developer version and using Management studio 2008 to access it. Anyone have any idea what could be going on?

Answers

  • Proposed as answer by Raul Garcia — MS Microsoft employee Friday, January 16, 2009 6:42 AM
  • Marked as answer by Mangal Pardeshi Monday, January 26, 2009 4:30 AM

All replies

  • Proposed as answer by Raul Garcia — MS Microsoft employee Friday, January 16, 2009 6:42 AM
  • Marked as answer by Mangal Pardeshi Monday, January 26, 2009 4:30 AM

I tried the examples of the page and got the same error each time

An Error Occured while executing a Transact-SQL Statement or batch
User does not have permission to perform this action( Microsoft SQL Server, Error:15247) None

No, You don’t need to install again, you just need to give rights to your login by logging in with sysadmin user. If server is running on mixed mode, log in with sa user and give your login required permissions.

First try to log in with sa, if sa is not enable, tell us, I will guide you accordingly. Mangal Pardeshi. You can turn off the SUN, but I’m still gonna shine. 😉

Did you guys solve this thing in the end?

I’m currently trying to do the same — having a similar issue getting the correct permission in SSME 2008 — right clicking so I can work as system admin — still no luck — final explaination confused me — being a newbie.

If your sa account is disabled , login with windows authentication and make sure you have your authentication mode set to MIXED mode and now you can enable your sa login using

ALTER LOGIN [sa] ENABLE

Thanks for responding. Appreciated.

Alas, in following your instructions I have a problem when I attempt to change authentication mode to mixed.

The message being:

TITLE: Microsoft SQL Server Management Studio
——————————

Alter failed for Server ‘PMC-PC\sqlexpress’. (Microsoft.SqlServer.Smo)

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

xp_regread() returned error 5, ‘Access is denied.’
xp_regread() returned error 5, ‘Access is denied.’
xp_regread() returned error 5, ‘Access is denied.’ (Microsoft SQL Server, Error: 22001)

Читайте также:  Aft huaqin selected file error

Needless to say, at this stage I have no idea what the resolution to all this is, so any more thoughts would be extremely appreciated!

When you change some server level settings with respect to SQL server , that has to be updated in the windows registry.

This kind of server level changes like authentication modes are to be done by a login that has sysadmin privilege in the SQL server . By default sysadmin logins has got privileges to execute xp_regwrite

Stored procs . If you are using windows vista , try starting the SSMS with run as administrator option.

Bottom line : this is a permission issue so perform this action with sysadmin account and you will succeed , remember to restart SQL services once the authentication mode is changed.

I’m having the same problem on Windows 7. Sql doesn’t recognize Windows 7 Administrative privileges so it denies permission to grant any changes to Sql Server. Can’t add users, can’t change permissions for users. Can’t use sa ID because don’t know the password set up by the system. It’s not my Windows admin password. It’s a viscious circle — want to add user with admin level permission but have to have admin level permission to do so.

Thanks for any help.

I am stuck in this same scenario. I am a local administrator on the box, in the administrators group. I cannot login as sa because the account is disabled and because mixed-mode authentication is off. It will not let me enable the user, or enable mixed mode authentication (the xp_regread() error). I cannot add «sysadmin» rights to any logins because «User does not have permission to perform this action.» I even tried changing the service to run as me instead of Network Service. I tried running as the user named «Administrator»

It is a bit strange that I can modify any single bit on the entire hard drive, delete, overwrite, and modify files. Yet I can’t change the permissions on something. This has to be a bug of some kind. FYI: This is SQL Server 2008 Express x64 w/ SP2 on Windows 7 x64.

I found a solution. Basically, run SQL server in Single-user maintenance mode, and you will get access to the server again. You must be an administrative user for this to work.

NOTE: In all of the examples below, you may have to change parameters or command-lines based on your server name and instance name.

I. Force SQL server to support mixed-mode authentication.
1. Run REGEDIT
2. Go to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQLServer
NOTE: This key may vary slightly based on the installed version and instance name.
3. Set «LoginMode» to 2.
4. Restart SQL Server.
(Source: http://support.microsoft.com/kb/285097 )

II. Force SQL server to let you in temporarily
1. Go to services.
2. Stop SQL Server.
3. Grab the SQL server command-line (right click the service — properties). Mine is:
«C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\Binn\sqlservr.exe» -sSQLEXPRESS
4. Open an administrative command prompt.
5. Run the command-line from step 3, but add -m -c for single-user maintenance mode command-line.
6. Open another administrative command prompt.
7. Run «sqlcmd -S localhost\SQLEXPRESS» from that same directory (replace with your server and instance name)
8. Now you can do all the stuff everyone told you to do that didn’t work. For example, to create a hero user with administrative accss:

9. QUIT and close the command-prompt
10. Go to the SQL Server command-line window and hit ctrl+C. It will prompt «Do you wish to shutdown SQL Server (Y/N)?» and enter Y.
11. Close the command-prompt
(Source: http://msdn.microsoft.com/en-us/library/dd207004.aspx )

III. Finally, login using your hero:
1. Restart the SQL Server service
2. Login using SQL Server authentication as the user «hero» with password «123»
3. *BAM* now you are in. Now give yourself sysadmin access and delete the temporary user.

Источник

Оцените статью
toolgir.ru
Adblock
detector