How to make corrupted DB working

Hi all!
About a month ago I had problems with SQL server, had to stop the sql server
process. After starting it I had to recover master DB. Everything was
looking good. But today I tried to grant DB access and found
sp_grantdbaccess missing.
After some experiments I found out a strange problem: I cannot create ANY
procedure on master DB (I have enough rights :) ).
Example: running script
CREATE PROC xxx AS SELECT 1
returns "There is already an object named 'xxx' in the database.", xxx is
any name.
This happens even on another SQL Server on db, that we backup/restored from
corrupted master db.
Any suggestions are welcomeThe correct way is to restore the last valid backup. If you don't have it,
you can try to rebuild the master database using Rebuildm.Exe command propt
utility (check for the syntax in Books OnLine). Then you will have to
recreate logins (maybe you can still generate scripts, so this task can be
easier?), attach all of the databases (check the sp_attach_db system SP) and
potentialy map logins to database users again (check the
sp_change_users_login for SQL logins and
http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/Q298/8/97.asp&NoWebContent=1
for Windows logins).
--
Dejan Sarka, SQL Server MVP
FAQ from Neil & others at: http://www.sqlserverfaq.com
Please reply only to the newsgroups.
PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Sergei Almazov" <almazik@.ukr.net> wrote in message
news:e7v2x8teDHA.1820@.TK2MSFTNGP10.phx.gbl...
> Hi all!
> About a month ago I had problems with SQL server, had to stop the sql
server
> process. After starting it I had to recover master DB. Everything was
> looking good. But today I tried to grant DB access and found
> sp_grantdbaccess missing.
> After some experiments I found out a strange problem: I cannot create ANY
> procedure on master DB (I have enough rights :) ).
> Example: running script
> CREATE PROC xxx AS SELECT 1
> returns "There is already an object named 'xxx' in the database.", xxx is
> any name.
> This happens even on another SQL Server on db, that we backup/restored
from
> corrupted master db.
> Any suggestions are welcome
>

How to know if a user has UNSAFE ASSEMBLY permission

Hi All,

How can I know if a user has UNSAFE ASSEMBLY permission on master database by executing query.

Please assist its really urgent, as I m stuck on this.

See SQL Server 2005 Books Online:
ALTER ASSEMBLY (Transact-SQL)
http://msdn2.microsoft.com/en-us/library/ms186711.aspx

Creating an Assembly

http://msdn2.microsoft.com/en-us/library/ms345106.aspx

Users don’t have UNSAFE ASSEMBLY permission on master database but maybe you are looking for this:

SELECT pr.name

FROM sys.server_principals pr

INNER JOIN sys.server_permissions pe

ON pr.principal_id = pe.grantee_principal_id

WHERE pe.permission_name = 'EXTERNAL ACCESS ASSEMBLY'

If you want to change permision on as assembly:

To alter an assembly whose existing permission set is UNSAFE requires membership in the sysadmin fixed server role.

To change the permission set of an assembly to UNSAFE requires membership in the sysadmin fixed server role.

If you are creating assemblies:

We recommend that the TRUSTWORTHY Database Property on a database not be set to ON only to run common language runtime (CLR) code in the server process. Instead, we recommend that an asymmetric key be created from the assembly file in the master database. A login mapped to this asymmetric key must then be created, and the login must be granted EXTERNAL ACCESS ASSEMBLY or UNSAFE ASSEMBLY permission.

USE master GOCREATE ASYMMETRIC KEY SQLCLRTestKey FROM EXECUTABLE FILE = 'C:\MyDBApp\SQLCLRTest.dll'CREATE LOGIN SQLCLRTestLogin FROM ASYMMETRIC KEY SQLCLRTestKeyGRANT EXTERNAL ACCESS ASSEMBLY TO SQLCLRTestLogin GO

|||

Hey Ikh,

Truly speaking, I m not finding words to thank you.

Your reply is exactly what I was looking for.

Thank you very …………………… much .

Actually the scenario at my end is that. In our project we have used an Assembly with UNSAFE Permissions. Now before installing the product we need to check whether the user has permissions to Access the UNSAFE ASSEMBLY.

Your reply is the exact answer to my question

Thanks again for spending time in replying to me.

Have a good time ahead!

How to know a Server IP address?

Hi
I read that with the next instruction in the SQL Query Analyzer I can know
the IP address of my actual server:
exec master..xp_cmdshell 'ipconfig'
However, it shows 9 rows with additional information I don't want.
I just want the IP address.
How can I do this?
Thanks
Gonzalo,
PING? but will return 3 rows as well. Possibly xp_regread (undoc in Master
db) to read key containing IP address?
HTH
Jerry
"Gonzalo Torres" <condormix2001@.yahoo.com.mx> wrote in message
news:u1eJ0p10FHA.3924@.TK2MSFTNGP14.phx.gbl...
> Hi
> I read that with the next instruction in the SQL Query Analyzer I can know
> the IP address of my actual server:
> exec master..xp_cmdshell 'ipconfig'
> However, it shows 9 rows with additional information I don't want.
> I just want the IP address.
> How can I do this?
> Thanks
>
|||Gonzalo Torres wrote:
> Hi
> I read that with the next instruction in the SQL Query Analyzer I can
> know the IP address of my actual server:
> exec master..xp_cmdshell 'ipconfig'
> However, it shows 9 rows with additional information I don't want.
> I just want the IP address.
> How can I do this?
> Thanks
Insert the output from xp_cmdshell into a temp table and parse it as
needed. Run IPCONFIG from the command prompt with the "/?" parameter for
a list of options that may help you reduce its output.
David Gugick
Quest Software
www.imceda.com
www.quest.com

How to know a Server IP address?

Hi
I read that with the next instruction in the SQL Query Analyzer I can know
the IP address of my actual server:
exec master..xp_cmdshell 'ipconfig'
However, it shows 9 rows with additional information I don't want.
I just want the IP address.
How can I do this?
ThanksGonzalo,
PING? but will return 3 rows as well. Possibly xp_regread (undoc in Master
db) to read key containing IP address?
HTH
Jerry
"Gonzalo Torres" <condormix2001@.yahoo.com.mx> wrote in message
news:u1eJ0p10FHA.3924@.TK2MSFTNGP14.phx.gbl...
> Hi
> I read that with the next instruction in the SQL Query Analyzer I can know
> the IP address of my actual server:
> exec master..xp_cmdshell 'ipconfig'
> However, it shows 9 rows with additional information I don't want.
> I just want the IP address.
> How can I do this?
> Thanks
>|||Gonzalo Torres wrote:
> Hi
> I read that with the next instruction in the SQL Query Analyzer I can
> know the IP address of my actual server:
> exec master..xp_cmdshell 'ipconfig'
> However, it shows 9 rows with additional information I don't want.
> I just want the IP address.
> How can I do this?
> Thanks
Insert the output from xp_cmdshell into a temp table and parse it as
needed. Run IPCONFIG from the command prompt with the "/?" parameter for
a list of options that may help you reduce its output.
David Gugick
Quest Software
www.imceda.com
www.quest.com

How to know a Server IP address?

Hi
I read that with the next instruction in the SQL Query Analyzer I can know
the IP address of my actual server:
exec master..xp_cmdshell 'ipconfig'
However, it shows 9 rows with additional information I don't want.
I just want the IP address.
How can I do this?
ThanksGonzalo,
PING? but will return 3 rows as well. Possibly xp_regread (undoc in Master
db) to read key containing IP address?
HTH
Jerry
"Gonzalo Torres" <condormix2001@.yahoo.com.mx> wrote in message
news:u1eJ0p10FHA.3924@.TK2MSFTNGP14.phx.gbl...
> Hi
> I read that with the next instruction in the SQL Query Analyzer I can know
> the IP address of my actual server:
> exec master..xp_cmdshell 'ipconfig'
> However, it shows 9 rows with additional information I don't want.
> I just want the IP address.
> How can I do this?
> Thanks
>|||Gonzalo Torres wrote:
> Hi
> I read that with the next instruction in the SQL Query Analyzer I can
> know the IP address of my actual server:
> exec master..xp_cmdshell 'ipconfig'
> However, it shows 9 rows with additional information I don't want.
> I just want the IP address.
> How can I do this?
> Thanks
Insert the output from xp_cmdshell into a temp table and parse it as
needed. Run IPCONFIG from the command prompt with the "/?" parameter for
a list of options that may help you reduce its output.
David Gugick
Quest Software
www.imceda.com
www.quest.com