\nHere's another blog posting to answer a question from over a month ago.\n<\/p>\n
\nWith separation of users and schemas, its known that the CREATE USER DDL statement without a DEFAULT_SCHEMA parameter assigns a DEFAULT_SCHEMA of dbo. Which the new user usually has no access to. And that sp_adduser, for backward compatibility, will CREATE a SCHEMA named after the user and assign that SCHEMA as the user's default schema. Question was, "does sp_dropuser do the right thing and drop the schema named after the user?"\n<\/p>\n
\nShort answer is "yes, it does". Pretty easy to prove. Actually, sp_adduser can eventually call sp_grantdbaccess and sp_dropuser eventually can call sp_revokedbaccess, so…\n<\/p>\n
\ncreate login bob with password = 'A^#DNEfdfhkWD#*iubdwc )000ks1'
\ngo\n<\/p>\n
\nuse adventureworks
\ngo\n<\/p>\n
\nsp_grantdbaccess 'bob'
\ngo\n<\/p>\n
\nselect * from sys.schemas — bob schema is there
\ngo\n<\/p>\n
\nsp_revokedbaccess 'bob'
\ngo\n<\/p>\n
\nselect * from sys.schemas — bob schema is gone
\ngo\n<\/p>\n
\nThe more intriguing thing is when you check this by using the OBJECT_DEFINITION system function:\n<\/p>\n
\n— creates schema
\nPRINT OBJECT_DEFINITION(OBJECT_ID('sys.sp_grantdbaccess'))\n<\/p>\n
\n— drops schema
\nPRINT OBJECT_DEFINITION(OBJECT_ID('sys.sp_revokedbaccess'))\n<\/p>\n
\nSure enough, there is nicely commented code in sp_revokedbaccess to drop a schema. BUT rather than using the DROP SCHEMA DDL statement, it looks like this:\n<\/p>\n
\nEXEC %%Owner(Name = @name_in_db).DropSchema(OwnerType = 1)\n<\/p>\n
\nHmmm….<\/p>\n","protected":false},"excerpt":{"rendered":"
Here's another blog posting to answer a question from over a month ago. With separation of users and schemas, its known that the CREATE USER DDL statement without a DEFAULT_SCHEMA parameter assigns a DEFAULT_SCHEMA of dbo. Which the new user usually has no access to. And that sp_adduser, for backward compatibility, will CREATE a SCHEMA […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[28,34,40],"tags":[],"class_list":["post-816","post","type-post","status-publish","format-standard","hentry","category-sql-server-2005","category-sql-server-schemas","category-transact-sql"],"yoast_head":"\n