MSSQL Table Login Name modification

One, modify the database Telnet account (of course, can also be manually built in SQL database)

Use ABC

Go

exec sp_change_users_login ' update_one ', ' abc_f ', ' abc_f '

Go

After the database import is complete, there is another place to confirm, that is, the general customer's table and the owner of the stored procedure is the default dbo, under this kind of permission, the user Abc_f to the table is read and write permission, you can add the delete table, but you cannot change the table structure of the existing owner-dbo table, for the stored procedure , Abc_f does not have any read and write execution permissions, which can cause problems for the user program.

From the principle, the solution has two, expand the database user's permissions, or the owner of these tables and stored procedures modified to Abc_f, the previous method involves security issues, do not allow such operations, so only the latter method can be used.

Second, the basic modified object owner of the stored procedures to use the following methods:

Use ABC

Go

EXEC sp_changeobjectowner ' tablename ', ' Abc_f '

Go

Third, the above statement can only change a single object, cannot implement bulk modification, but for tables, SQL Server comes with a stored procedure that traverses the table sp_msforeachtable, for modifying all the table owner's statement as follows:

Use ABC

Go

Sp_msforeachtable ' sp_changeobjectowner '? ', ' Abc_f '

Go

Note that the fields in the quotation marks need to be delimited with two single quotes.

Four, if the customer database does not have stored procedures and other objects, after completing the above operation, you should be able to find that all the table owner of the customer database has been replaced by the customer's users, so that the table can be modified. If the customer database also contains other objects such as stored procedures, because SQL Server itself does not provide a msforeachprodure-like stored procedure, this type of object cannot be modified in bulk. The workaround is to use your own write stored procedure to implement, the following code can establish a Msforeachobject stored procedure for traversing the various objects in the database, just put this code into the Query Analyzer execution.

Use MASTER

GO

CREATE proc Sp_msforeachobject

@objectType Int=1,

@command1 nvarchar (2000),

@replacechar nchar (1) = N '? ',

@command2 nvarchar () = null,

@command3 nvarchar () = null,

@whereand nvarchar () = null,

@precommand nvarchar () = null,

@postcommand nvarchar (.) = null

As

/* This proc returns one or more rows for each table (optionally, matching @where), with each table defaulting to its

Own result set */

/* @precommand and @postcommand May is used to force a single result set via a temp table. */

/* Preprocessor won ' t replace within quotes so has to use STR (). */

declare @mscat nvarchar (12)

Select @mscat = LTrim (str (CONVERT (int, 0x0002)))

If (@precommand is not null)

EXEC (@precommand)

/* Defined @isobject for Save object type */

Declare @isobject varchar (256)

Select @isobject = Case @objectType If 1 then ' isusertable '

When 2 Then ' Isview '

When 3 Then ' Istrigger '

When 4 Then ' Isprocedure '

When 5 Then ' IsDefault '

When 6 then ' Isforeignkey '

When 7 Then ' Isscalarfunction '

When 8 Then ' isinlinefunction '

When 9 then ' IsPrimaryKey '

When ten then ' Isextendedproc '

When one and then ' Isreplproc '

When the ' Isrule '

End

/* Create the SELECT */

/* Use @isobject variable isstead of isusertable String */

EXEC (n ' Declare hcforeach cursor global for select ' [' + REPLACE (user_name (UID), n '] ', ' n ']] ') + ' + ' + ' + ' +

REPLACE (object_name (id), n "]", n "]]") + "" ' From Dbo.sysobjects o '

+ N ' where OBJECTPROPERTY (o.id,) = 1 ' +n ' and o.category & ' + @mscat + N ' = 0 '

+ @whereand)

DECLARE @retval int

Select @retval = @ @error

if (@retval = 0)

exec @retval = Sp_msforeach_worker @command1, @replacechar, @command2, @command3

if (@retval = 0 and @postcommand is not null)

EXEC (@postcommand)

Return @retval

GO

Five, the previous code is a stored procedure created by cloning msforeachtable that can be used to traverse various objects, using the following methods:

Use ABC

Go

EXEc sp_msforeachobject @command1 = "sp_changeobjectowner '? ', ' Abc_f '", @objectType =1

Go

The last parameter, ObjectType, represents the object type, 1 is a table, 2 is a view, 3 is a trigger, 4 is a stored procedure, and 7 can modify the client's own defined function.

This command can be used to change the owner of a variety of objects to the client's database account, you can resolve the above database permissions issues

Collected in 2009-04-02

This article is from the "Five Corners" blog, please be sure to keep this source http://hi289.blog.51cto.com/4513812/1754984

MSSQL Table Login Name modification